ABAP/SQL: How do I get classification of specific Column Values based on IDs and on multiple conditions on ID?

Question

I have the following internal table:

ID	Text	Origin
1	Text1	Word
1	Text1	Word
1	Text1	Excel
1	Text1	PowerPoint
2	Text1	Excel
2	Text1	PowerPoint
3	Text1	PowerPoint
3	Text1	Excel
3	Text1	Excel
3	Text1	Excel

and Im trying to get result:

ID	Origin	Classification
1	Word	Triple_Word_Excel_PowerPoint
1	Word	Triple_Word_Excel_PowerPoint
1	Excel	Triple_Word_Excel_PowerPoint
1	PowerPoint	Triple_Word_Excel_PowerPoint
2	Excel	Double_Excel_PowerPoint
2	PowerPoint	Double_Excel_PowerPoint
3	Word	Double_Excel_Word
3	Excel	Double_Excel_Word
3	Excel	Double_Excel_Word
3	Excel	Double_Excel_Word

I tried to filter the ID using a loop call and check which value is present in the Origin column. But I just can't get it. If there is a better solution using a SELECT call on the internal table, then i could try that as well.

My Code example:

    DATA: lv_current_id   TYPE string VALUE ' ',
      lv_origin_count TYPE i VALUE 0,
      lv_origin_text  TYPE string VALUE ' '.

LOOP AT lt_internal_table INTO DATA(ls_data).

  CLEAR: lv_origin_count, lv_origin_text.

  " Check, if origin is processed
  IF NOT ( ls_data-origin IN lt_origin ).
    " Save origin in temporary list
    APPEND ls_data-origin TO lt_origin.

    " Count occurrence of origin per id
    LOOP AT lt_data INTO DATA(ls_data2) WHERE id = ls_data-id AND origin = ls_data-origin.
      ADD 1 TO lv_origin_count.
    ENDLOOP.

    " Create cext for classification columnText für die Anzahl_Herkunft erstellen
    CASE lv_origin_count.
      WHEN 1.
        lv_origin_text = 'Single' && ls_data-origin.
      WHEN 2.
        lv_origin_text = 'Double' && ls_data-origin && ls_data-origin.
      WHEN 3.
        lv_origin_text = 'Triple' && ls_data-origin && ls_data-origin && ls_data-origin.
    ENDCASE.

    " Save result in internal result table 
    APPEND VALUE #( id = ls_data-id text = ls_data-text origin = ls_data-origin classification = lv_origin_text ) TO lt_result.
  ENDIF.
ENDLOOP.

Answer 1

Simple option would be to generate ID & Classification combination separately into another internal table and use it while looping over the initial table to construct final table. Code snippet is as shown below.

REPORT zabap_sql.

TYPES BEGIN OF ty1.
TYPES id TYPE i.
TYPES text TYPE char10.
TYPES origin TYPE char10.
TYPES END OF ty1.

TYPES BEGIN OF ty_cls.
TYPES id TYPE i.
TYPES classification TYPE string.
TYPES END OF ty_cls.

TYPES BEGIN OF tyresult.
TYPES id TYPE i.
TYPES origin TYPE char10.
TYPES classification TYPE char50.
TYPES END OF tyresult.

DATA lt_cls TYPE STANDARD TABLE OF ty_cls.
DATA ls_cls TYPE ty_cls.
DATA lv_id_string TYPE string.
DATA lv_id_count TYPE i.
DATA lt_internal_table TYPE STANDARD TABLE OF ty1.
DATA lt_result TYPE STANDARD TABLE OF tyresult.

START-OF-SELECTION.

  lt_internal_table = VALUE #(
                ( id = 1 text = 'Text1' origin = 'Word' )
                ( id = 1 text = 'Text1' origin = 'Excel' )
                ( id = 1 text = 'Text1' origin = 'PowerPoint' )
                ( id = 1 text = 'Text1' origin = 'Word' )
                ( id = 2 text = 'Text1' origin = 'Excel' )
                ( id = 2 text = 'Text1' origin = 'PowerPoint' )
                ( id = 3 text = 'Text1' origin = 'Excel' )
                ( id = 3 text = 'Text1' origin = 'Word' )
                ( id = 3 text = 'Text1' origin = 'Excel' )
                ( id = 3 text = 'Text1' origin = 'Excel' )
               ).
  SELECT FROM @lt_internal_table AS lt
      FIELDS id, origin
  GROUP BY id, origin
      ORDER BY id,origin
  INTO TABLE @DATA(lt_temp) .

  LOOP AT lt_temp INTO DATA(ls_temp) GROUP BY ls_temp-id.

    LOOP AT GROUP ls_temp INTO DATA(ls_temp_groupped).

      lv_id_count = lv_id_count + 1.

      IF lv_id_string IS INITIAL.
        lv_id_string = ls_temp_groupped-origin.
      ELSE.
        CONCATENATE lv_id_string ls_temp_groupped-origin INTO lv_id_string SEPARATED BY '_'.
      ENDIF.
    ENDLOOP.

    CASE lv_id_count.
      WHEN 1. CONCATENATE 'Single' lv_id_string INTO lv_id_string SEPARATED BY '_'.
      WHEN 2. CONCATENATE 'Double' lv_id_string INTO lv_id_string SEPARATED BY '_'.
      WHEN 3. CONCATENATE 'Triple' lv_id_string INTO lv_id_string SEPARATED BY '_'.
    ENDCASE.

    ls_cls = VALUE #( id = ls_temp-id classification = lv_id_string ).
    APPEND ls_cls TO lt_cls.
    CLEAR: lv_id_count, lv_id_string.

  ENDLOOP.

  SORT lt_cls BY id.

  LOOP AT lt_internal_table INTO DATA(ls_data).
    READ TABLE lt_cls INTO DATA(ls_cls1) WITH KEY id = ls_data-id.
    " Save result in internal result table
    APPEND VALUE #( id = ls_data-id origin = ls_data-origin classification = ls_cls1-classification ) TO lt_result.
  ENDLOOP.

  cl_demo_output=>display_data( lt_result ).

Output is as below.

ID  ORIGIN      CLASSIFICATION
1   Word        Triple_Excel_PowerPoint_Word
1   Excel       Triple_Excel_PowerPoint_Word
1   PowerPoint  Triple_Excel_PowerPoint_Word
1   Word        Triple_Excel_PowerPoint_Word
2   Excel       Double_Excel_PowerPoint
2   PowerPoint  Double_Excel_PowerPoint
3   Excel       Double_Excel_Word
3   Word        Double_Excel_Word
3   Excel       Double_Excel_Word
3   Excel       Double_Excel_Word

Answer 2

Your code does not compile, so I guess you have written the code without verifying it. Please fix it.

It is also far from being minimal e.g. the text from the input is not needed according to the information you have provided.

It's not clear in your question what result you currently obtain.

Note that I recommend that you focus on first generating an intermediate result (the final result is obvious to obtain), that would also simplify your question:

1   Triple_Word_Excel_PowerPoint
2   Double_Excel_PowerPoint
3   Double_Excel_Word

If you have missed just a few obvious lines of code, this code would be a valid minimal reproducible example. As you can see, it generates data only for the ID 1, so a quick basic debug would tell you that the problem is with the condition which prevents from processing more than one ID:

REPORT.
TYPES: BEGIN OF ty_input,
         id     TYPE i,
         origin TYPE string,
       END OF ty_input.
TYPES: BEGIN OF ty_result,
         id             TYPE i,
         origin         TYPE string,
         classification TYPE string,
       END OF ty_result.
TYPES ty_input_table  TYPE STANDARD TABLE OF ty_input WITH EMPTY KEY.
TYPES ty_result_table TYPE STANDARD TABLE OF ty_result WITH EMPTY KEY.
DATA lv_current_id   TYPE string          VALUE ' '.
DATA lv_origin_count TYPE i               VALUE 0.
DATA lv_origin_text  TYPE string          VALUE ' '.
DATA lt_origin       TYPE RANGE OF string.

DATA(lt_internal_table) = VALUE ty_input_table( ( id = 1 origin = 'Word      ' )
                                                ( id = 1 origin = 'Word      ' )
                                                ( id = 1 origin = 'Excel     ' )
                                                ( id = 1 origin = 'PowerPoint' )
                                                ( id = 2 origin = 'Excel     ' )
                                                ( id = 2 origin = 'PowerPoint' )
                                                ( id = 3 origin = 'PowerPoint' )
                                                ( id = 3 origin = 'Excel     ' )
                                                ( id = 3 origin = 'Excel     ' )
                                                ( id = 3 origin = 'Excel     ' ) ).
DATA(lt_result) = VALUE ty_result_table( ).

APPEND VALUE #( sign   = 'I'
                option = 'EQ'
                low    = space ) TO lt_origin.
LOOP AT lt_internal_table INTO DATA(ls_data).

  CLEAR: lv_origin_count, lv_origin_text.

  " Check, if origin is processed
  IF NOT ( ls_data-origin IN lt_origin ).
    " Save origin in temporary list
    APPEND VALUE #( sign   = 'I'
                    option = 'EQ'
                    low    = ls_data-origin ) TO lt_origin.

    " Count occurrence of origin per id
    LOOP AT lt_internal_table INTO DATA(ls_data2) WHERE id = ls_data-id AND origin = ls_data-origin.
      lv_origin_count = lv_origin_count + 1.
    ENDLOOP.

    " Create cext for classification columnText für die Anzahl_Herkunft erstellen
    CASE lv_origin_count.
      WHEN 1.
        lv_origin_text = 'Single' && ls_data-origin.
      WHEN 2.
        lv_origin_text = 'Double' && ls_data-origin && ls_data-origin.
      WHEN 3.
        lv_origin_text = 'Triple' && ls_data-origin && ls_data-origin && ls_data-origin.
    ENDCASE.

    " Save result in internal result table
    APPEND VALUE #( id             = ls_data-id
                    origin         = ls_data-origin
                    classification = lv_origin_text ) TO lt_result.
  ENDIF.
ENDLOOP.

It currently generates this result table:

Row  ID  ORIGIN      CLASSIFICATION
=====================================
1    1   Word        DoubleWordWord
2    1   Excel       SingleExcel
3    1   PowerPoint  SinglePowerPoint