topic Re: I have problem using Previous Function in QlikView

I have problem using Previous Function

jim_chan — Mon, 24 May 2021 14:24:59 GMT

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.

Re: I have problem using Previous Function

QFabian — Mon, 24 May 2021 22:39:55 GMT

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;

Re: I have problem using Previous Function

jim_chan — Mon, 24 May 2021 23:48:45 GMT

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

Re: I have problem using Previous Function

QFabian — Tue, 25 May 2021 01:13:36 GMT

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

Re: I have problem using Previous Function

jim_chan — Tue, 25 May 2021 01:19:45 GMT

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

Re: I have problem using Previous Function

jim_chan — Tue, 25 May 2021 01:27:42 GMT

hi @QFabian ,

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

rgds

jim

Re: I have problem using Previous Function

jim_chan — Tue, 25 May 2021 02:26:51 GMT

hi @QFabian ,

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

rgds

Jim