topic Re: Load files and compare automatically in QlikView

Load files and compare automatically

ManiSK — Thu, 26 Mar 2020 09:10:01 GMT

Can some one help me on below query?

I have some 100 files, i want to load one file and compare it to other and save the output.

For example: first i want to load day 1 file compare it to day 2 file save the missing data in ouput.csv

next load day 2 file compare it to day3 file save the missing data in ouput.csv

like this it should keep on run in single load ...

Files are distinguished with date stamp.

below is the example which im trying out.

table:

load user_id,

status

from day1.csv

left join (table)

load user_id,

status,

'y' as flag

from day2.csv

output:

Load user_id

if(flag='y',status,'excluded') as status

resident table;

like above it should run automtically for all the days, for loop we have to use

Thanks,

Re: Load files and compare automatically

Arslan_Ahmed — Thu, 26 Mar 2020 07:38:05 GMT

I believe what you are looking for is an incremental load

Follow these steps

1. Load 1st file initial file ;
2. Create a variable and store max value form Table;
3. Concatenate the second table and use the where clause (Where Date > Var)
4. Use the star * in the concatenated version path file to automatically pick up all the files.

A detailed example:
https://community.qlik.com/t5/QlikView-App-Development/Incremental-Load-not-working/m-p/1636553

Re: Load files and compare automatically

ManiSK — Thu, 26 Mar 2020 07:55:54 GMT

Hi Arlsan,

i want to compare the files and save the difference of those two files

like day 1 and day2 then day2 and day3 then day3 and day4 in a single run.

May i know how incremental load helps here?

can you pls provide some examples? for my understanding

Thanks,

Re: Load files and compare automatically

Arslan_Ahmed — Thu, 26 Mar 2020 08:45:35 GMT

If you could possibly share a sample of your data or code you've written , will be easier to understand .

thanks

Re: Load files and compare automatically

ManiSK — Thu, 26 Mar 2020 09:10:30 GMT

updated the post please chekc

Re: Load files and compare automatically

Frank_Hartmann — Thu, 26 Mar 2020 13:14:29 GMT

Have a look at the attached sample

hope this helps

Re: Load files and compare automatically

ManiSK — Fri, 27 Mar 2020 04:42:47 GMT

Hi Frank,

Thank you so this works fine. file name is not day1 day2 i gave that for example

Actually the real file name is all_transfer_event_users-2020-03-18-19-26-41.csv like this with date stamp, so in this case how below loop will work? can you please help me on this?

j=$(j)+1;
i=$(i)+1;

next x;

Re: Load files and compare automatically

Frank_Hartmann — Fri, 27 Mar 2020 08:50:42 GMT

Please adapt according your needs.

let i = '2020-03-18'; let j = '2020-03-19'; For x=1 to 2 //with 3 Files you have 2 loops i= date(i,'YYYY-MM-DD') j = date(j,'YYYY-MM-DD') Map: Mapping LOAD B,A FROM [all_transfer_event_users-$(i)*.xlsx] (ooxml, embedded labels, table is Tabelle1); tmp: LOAD Distinct A, B FROM [all_transfer_event_users-$(j)*.xlsx] (ooxml, embedded labels, table is Tabelle1) where ApplyMap('Map',B,0)=0; store tmp into Differences_Table_$(j)_$(i).csv(txt);drop table tmp; i = i+1; j = j+1; next x;