Fill up null cell with next value

[katetsan]

Hi Experts,

I would like to fill up the missing month year with next value.

I do generate the full month year during specific period successfully, but I couldn't fill up the null cell with next value.

Is there anyone could give me some hint?

Thanks in advance.

Kate

Load script as below:

//********************************************************************************

MARDH_TEMP:
LOAD
*,
WERKS&'|'&MATNR&'|'&LGORT&'|'&DATE(DATE#(MONTHEND(MAKEDATE(NUM#(LFGJA),NUM#(LFMON),1)),'YYYY/M/D'),'YYYY/M/D') AS [MASTER_KEY],
WERKS&'|'&MATNR&'|'&LGORT AS [TEMP_KEY],
DATE(DATE#(MONTHEND(MAKEDATE(NUM#(LFGJA),NUM#(LFMON),1)),'YYYY/M/D'),'YYYY/M/D') as [DATE]
FROM [$(vSAPDataPath)ECC/MARDH*.qvd](qvd)
;

NoConcatenate
MARDH_MIN_MAX:
Load
[TEMP_KEY],
DATE(MIN(DATE),'YYYYMM') AS [MIN_DATE],
DATE(MAX(DATE),'YYYYMM') AS [MAX_DATE]
RESIDENT MARDH_TEMP
GROUP BY [TEMP_KEY];

NoConcatenate
YEARMONTH_TEMP:
LOAD
[TEMP_KEY],
[TEMP_KEY]&'|'&DATE(MONTHEND([MIN_DATE],IterNo()-1),'YYYY/M/D') AS [MASTER_KEY],
DATE(MONTHEND([MIN_DATE],IterNo()-1),'YYYY/M/D') as DATE
RESIDENT MARDH_MIN_MAX
WHILE MONTHEND([MIN_DATE],IterNo()-1) <= MAX_DATE;
DROP FIELD [TEMP_KEY] FROM MARDH_TEMP;
DROP FIELD [TEMP_KEY] FROM YEARMONTH_TEMP;

NoConcatenate
YEARMONTH:
LOAD
*
RESIDENT YEARMONTH_TEMP;
LEFT JOIN
LOAD
[MASTER_KEY],
MATNR,
WERKS,
LGORT,
LABST,
UMLME,
INSME,
EINME,
SPEME,
RETME,
VKLAB,
VKUML
RESIDENT MARDH_TEMP;
DROP TABLES MARDH_MIN_MAX, MARDH_TEMP, YEARMONTH_TEMP;

[andoryuu · Accepted solution]

Try this:
1. Load your table as above.
2. Sort the table in descending order.
3. Use Peek() to get the value from the previous row (would have been next in original sorting).

[katetsan]

Hi Andoryuu,

Thanks for your advice. It works.