How to Sort and Select Maximum

[Former Member]

Hi all,

I have a list of data which shows the 'workflow' for Licenses (LIC_ID). Every time a License goes through another stage of the workflow action (AC_ID), another row is added to the table under a new unique identifier (ROW_ID).

What I need to do is find the last workflow action which happened to each License. I think this means I have to sort the table by LIC_ID and the ROW_ID, and then once the table is sorted, pick the maximum ROW_ID per LIC_ID (which will give me the last AC_ID).

Hence the after sorting the table looks something like

ROW_ID LIC_ID AC_ID

1 100 Start

18 100 Process

25 100 End

3 200 Start

9 200 Hold

30 200 Cancel

etc.

However I have no idea what the syntax would be to take the highest ROW_ID per LIC_ID.

Can anyone help?

Please let me know if this does not make sense!

Thanks

Mischa

Edited by: Mischa Gulseven on Aug 8, 2008 4:19 PM

[Former Member]

Hi all,

I have a list of data which shows the 'workflow' for Licenses (LIC_ID). Every time a License goes through another stage of the workflow action (AC_ID), another row is added to the table under a new unique identifier (ROW_ID).

What I need to do is find the last workflow action which happened to each License. I think this means I have to sort the table by LIC_ID and the ROW_ID, and then once the table is sorted, pick the maximum ROW_ID per LIC_ID (which will give me the last AC_ID).

Hence the after sorting the table looks something like

ROW_ID LIC_ID AC_ID

1 100 Start

18 100 Process

25 100 End

3 200 Start

9 200 Hold

30 200 Cancel

etc.

However I have no idea what the syntax would be to take the highest ROW_ID per LIC_ID.

Can anyone help?

Please let me know if this does not make sense!

Thanks

Mischa

Edited by: Mischa Gulseven on Aug 8, 2008 4:19 PM

[former_member194669]

For example your sorted internal table ITAB.

Then

sort itab by row_id descending lic_id descending.

loop at itab.
  at new of lic_id.
    move 'Y' to v_flag.
  endat.
  if v_flag eq 'Y'.
    move-corresponding itab to itab1.
    append itab1.
    clear v_flag.
  endif.
endloop.

Here ITAB1 contains the maximum ROW_ID per LIC_ID.

a®

[Former Member]

If this is in an internal table, you can try something like this.

LOOP AT tableid.

AT END OF LIC_ID.

WRITE:/ LIC_ID, ROW_ID.

ENDAT.

ENDLOOP.

[Former Member]

Hi,

Sort itab by LIC_ID ROW_ID

loop at itab.

at end of LIC_ID.

....here you will get the maximum of ROW_ID...

endat.

endloop.

Regards,

Subramanian

[Former Member]

All,

I really appreciate your help on this, but for some reason the code which you have all recommended "LOOP AT itab" is not working for me.

I am getting the error message

"At "LOOP AT itab" one of the additions "INTO", "ASSIGNING" or "TRANSPORTING NO FIELDS" is required in the 00 context."

Can anyone tell me where I'm going wrong?

Thanks again

Mischa

[Former Member]

Declare the internal table without header line and use

LOOP AT itab assigning <fs_itab>.

endloop.

or

LOOP AT itab into wa_itab.

endloop.

Edited by: shakeela Shibu on Aug 12, 2008 1:08 PM

[Former Member]

hi,

Try out like this.

data: define var1 and var2 of type lic_id and row_id.

Loop at itab.

at new lic_id.

clear var1.

var1 = itab-lic_id.

endat.

SELECT MAX( row_id )

INTO (var2 ) from ltab where lic_id = var1.

endloop.

hope this will help u.