Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I am not sure how to use the Previous function in my requirement.
Ok. i have a list of data, same ID but activated date and main key is different.
I expected result is:
1) Only load out the ID with latest activated_dt - mean in my data should only keep 26/1 records, and get rid of the old one - actviated dt on 22 jan.
2) the actual data is daily transaction, can have millions of records. user just want each ID to have 14 days of records only.
However, i have tried to use Previous Function, but ended up only able to get this. So, i would really need your guys help again.
I have my sample qvw and data as per attached.
Hi @jim_chan , please check this :
DATA:
LOAD
MAIN_KEY & ' | ' & ID as %_Key,
MAIN_KEY,
ID,
DATED_DT,
ACTIVATED_DT,
TOTAL_GB
FROM test_2subs_key.csv (txt, utf8, embedded labels, delimiter is ',', msq)
Where
DATED_DT >= (today()-14);
LATEST_RECORD:
Load
%_Key,
max(ACTIVATED_DT) as Max_ACTIVATED_DT
Resident DATA
Group By
%_Key;
exit script;
Hi @jim_chan , -DATED_DT >= (today()-14); it ia going to load records with that date from the last 14 days. Maybe you need another date field to use it, just change it
Hi @QFabian , thanks for your reply!
I have tried to change the where clause to - DATED_DT - ACTIVATED_DT <= 14;
but the old record come out again. ID with main key - 1111111_0
where should i change it?
Rgds
Jim
hi @QFabian ,
because my expected result is to load only 14 days of data from the activated_dt (26/1/2021) .
rgds
jim
hi @QFabian ,
sorry, not able to provide me the expected result. Hopefully, there are more replies on this issue.
rgds
Jim