Load files and compare automatically

[ManiSK]

Hi 

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,

[Arslan_Ahmed]

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

[ManiSK]

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,

[Arslan_Ahmed]

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

thanks

[ManiSK]

updated the post please chekc

[Frank_Hartmann]

Have a look at the attached sample

hope this helps

[ManiSK]

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;

[Frank_Hartmann · Accepted solution]

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;