I have a raw dataset that looks like this:
that I need to convert to looking like that for today, which is 18.12.2024:
It is in essentialy pivoting.
So far I managed to create the dynamic table type of the output table using RTTS and now need to find a way to populate it from the raw data.
The base logic is sumarization of quanitity based on date, werks and land:
_t0 column is sum for the day if wadat = erdat,
_older is sum of the day if erdat is more than a day older than current day
ABC is the sum of the day for all where ABC exists in ZUKRL
So far I am only able to sum based on simple group by:
t_data = VALUE #( FOR GROUPS g1 OF ls_row IN gt_data GROUP BY ( wadat = ls_row-wadat
werks = ls_row-werks
land1 = ls_row-land1
"erdat = ls_row-erdat
)
LET sum = REDUCE #( INIT line TYPE ty_sum_line
FOR members_g1 IN GROUP g1
NEXT line-wadat = members_g1-wadat
line-werks = members_g1-werks
line-land1 = members_g1-land1
line-lfimg = line-lfimg + members_g1-lfimg
)
IN ( wadat = g1-wadat
werks = g1-werks
land1 = g1-land1
lfimg = sum-lfimg
"lfimg_t0
"lfimg_older
"lfimg_abc
)
).
My idea is to fill lfimg_t0 lfimg_older lfimg_abc with the appropriate sums and then loop and populate the dynamic table. Could you help me out with the grouping logic, please.
Added for clarification after comments:
How to get values from group g_tot into these:
“lfimg_t0
“lfimg_older
“lfimg_abc
t_data2 = VALUE #( FOR GROUPS g1 OF ls_row IN gt_data GROUP BY ( wadat = ls_row-wadat
werks = ls_row-werks
land1 = ls_row-land1
)
FOR GROUPS g_tot OF row_tot IN gt_data GROUP BY ( wadat = row_tot-wadat
werks = row_tot-werks
)
LET sum = REDUCE #( INIT line TYPE ty_sum_line
FOR members_g1 IN GROUP g1
NEXT line-wadat = members_g1-wadat
line-werks = members_g1-werks
line-land1 = members_g1-land1
line-lfimg = line-lfimg + members_g1-lfimg
)
IN ( wadat = g1-wadat
werks = g1-werks
land1 = g1-land1
lfimg = sum-lfimg
"lfimg_t0
"lfimg_older
"lfimg_abc
)
).
… And I am thinking I need a field to store what group is this coming from in order to be able to populate the result table, because there are many different WERKS with even more LAND1
3
Here’s one way, but the code is quite complex, it will be easier to understand if you split it into method(s).
It compiles in ABAP 7.40 SP8.
DATA(intermediate_lines) = VALUE intermediate_lines(
FOR GROUPS <group_by_wadat_werks> OF <source_line> IN source_lines
GROUP BY ( wadat = <source_line>-wadat
werks = <source_line>-werks )
LET sums_by_wadat_werks_land1 = VALUE intermediate_lines(
FOR GROUPS <group_by_land1>
OF <source_line_2> IN GROUP <group_by_wadat_werks>
GROUP BY ( land1 = <source_line_2>-land1 )
LET sums = REDUCE sums(
INIT sums_2 TYPE sums
FOR <line_of_group_by_land1> IN GROUP <group_by_land1>
NEXT sums_2-lfimg = sums_2-lfimg
+ <line_of_group_by_land1>-lfimg )
IN
( wadat = <group_by_wadat_werks>-wadat
werks = <group_by_wadat_werks>-werks
land1 = <group_by_land1>-land1
lfimg = sums-lfimg ) )
sums_by_wadat_werks = VALUE intermediate_line(
LET sums_3 = REDUCE sums(
INIT sums_2 TYPE sums
FOR <line_sums_by_wadat_werks_lan> IN sums_by_wadat_werks_land1
NEXT sums_2-lfimg = sums_2-lfimg
+ <line_sums_by_wadat_werks_lan>-lfimg )
IN wadat = <group_by_wadat_werks>-wadat
werks = <group_by_wadat_werks>-werks
land1 = 'TOT'
lfimg = sums_3-lfimg )
IN
( LINES OF sums_by_wadat_werks_land1 )
( sums_by_wadat_werks ) ).
Code before the block above, to test it:
DATA(source_lines) = VALUE source_lines( werks = 'DE10'
lfimg = 1
( wadat = '20241217' land1 = 'DE' )
( wadat = '20241217' land1 = 'DE' )
( wadat = '20241217' land1 = 'FR' )
( wadat = '20241218' land1 = 'DE' ) ).
Code after the block above, to check the result (it’s the expected result):
ASSERT intermediate_lines = VALUE intermediate_lines(
werks = 'DE10'
( wadat = '20241217' land1 = 'DE' lfimg = 2 )
( wadat = '20241217' land1 = 'FR' lfimg = 1 )
( wadat = '20241217' land1 = 'TOT' lfimg = 3 )
( wadat = '20241218' land1 = 'DE' lfimg = 1 )
( wadat = '20241218' land1 = 'TOT' lfimg = 1 ) ).
Top declarations:
TYPES lfimg TYPE p LENGTH 7 DECIMALS 2.
TYPES:
BEGIN OF source_line,
wadat TYPE d,
werks TYPE c LENGTH 4,
land1 TYPE land1,
lfimg TYPE lfimg,
END OF source_line.
TYPES source_lines TYPE STANDARD TABLE OF source_line WITH EMPTY KEY.
TYPES:
BEGIN OF intermediate_line,
wadat TYPE d,
werks TYPE c LENGTH 4,
land1 TYPE land1,
lfimg TYPE lfimg,
END OF intermediate_line.
TYPES intermediate_lines TYPE STANDARD TABLE OF intermediate_line WITH EMPTY KEY.
TYPES:
BEGIN OF sums,
lfimg TYPE lfimg,
END OF sums.