I have a task in script to take data which is very previous before first value "ME21NU". But the problem that when I am making expression FirstValue(FINAL_FLAG)-1 as Indicator, I dont have any data, but NULL. Can you support, how it should looks like? Thanks in advance
TCODE&CHANGE_IND&AutoNumber(CHANGENR&TCODE&CHANGE_IND) as FINAL_FLAG
CDHDR_TMP2: load *, If(Left(FINAL_FLAG,6)='ME21NU',1,0) as Flag resident CDHDR_TMP; drop table CDHDR_TMP;
CDHDR_IND: Load CDHDR_KEY, Flag, FirstValue(FINAL_FLAG)-1 as Indicator resident CDHDR_TMP2 where Flag=1 group by CDHDR_KEY,Flag;
Left join (CDHDR_TMP2) Load* resident CDHDR_IND; Drop Table CDHDR_IND;
CDHDR: Load*, TCODE as TCODE1 resident CDHDR_TMP2 where Indicator=1; Drop table CDHDR_TMP2;
Are you looking to get the second value? May be you need to use another way to do this? Would you be able to share some sample data to show the kind of data you have (dummy mocked up data will work to) and explain what you are looking to get from it?
No, In my originaly formula, it was necessary to take last value of Date(the red square on the print screen) , but later we understood that date should be, the last one, before second ME21N(on the print screen it is green square). So I decided to make a key-FINAL_FLAG. Than another Flag, which indicates where is necessary ME21N (in source it could be 3-4 times of this ME21N). So the easiest way was, to take FirstValue of ME21NU (U-is indicator) and minus 1. So we will take date, which we need. All steps and keys in above part of script, which I attached are working, but when it begins expression FirstValue, there is 0 data.