I have problem using Previous Function

[jim_chan]

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.

[QFabian]

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;

[jim_chan]

Dear fabian,

Thanks for the script.

Can you tell me why use where clause -DATED_DT >= (today()-14);

In actual data, there are line of records from dated date 1 Jan 2021. By
applying this clause, does it means those with 1 Jan 2021 data will not be
loaded?

Rgds

Jim chan

[QFabian]

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

[jim_chan]

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

[jim_chan]

hi @QFabian , 

because my expected result is  to load only 14 days of data from the activated_dt (26/1/2021) . 

rgds 

jim

[jim_chan]

hi @QFabian ,

sorry, not able to provide me the expected result.  Hopefully, there are more replies on this issue.

rgds

Jim