Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik users,
I have a question with regards to data load in the script.
I have 3 different files which i will be getting each file in every week.
1. ABC(03.12.2017 - 04.09.2017).xlsx received on 04.12.2017
2. ABC(03.20.2017 - 04.16.2017).xlsx received on 04.19.2017
3. ABC(04.03.2017 - 04.30.2017).xlsx received on 05.03.2017.
the 3 files contains some data common since the data is repeating twice or thrice. So I want to load 1st file and when I load 2nd file also we have 03.20.2017 to 04.09.2017 data is double in the app. So I want data of those dates in the app to be replaced from 2nd file and load data avoiding duplicate values double.
How can I achieve it ?
Any help is appreciated !!
Use Incremental Load
Concept below.
Basically where not exists and where exists will help you
Do you have a primary key in your field? If it's the case, you have just to use not exists.
In my example, i use three .txt files. File 1 and file 2 have one common line, and file 2 and file 3 have one common line too.
File1:
LOAD [id__transaction,],
[Amount €]
FROM
C:\Users\jb\Desktop\Sample1.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
File2:
LOAD [id__transaction,],
[Amount €]
FROM
C:\Users\jb\Desktop\Sample2.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq) where not Exists ([id__transaction,]);
File3:
LOAD [id__transaction,],
[Amount €]
FROM
C:\Users\jb\Desktop\Sample3.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq) where not Exists ([id__transaction,]);
From the QVD: load the table for date field, fetch the Max date, drop the table.
Load the data from Database / excel by adding a where condition to filter the updated records.
concatenate the data with QVD file, check the duplicate values on some ID column using where exists(ID)
store the QVD file, Drop the table of data loaded from database / excel.
TableData:
LOAD [id__transaction,],
[Amount €]
FROM
(......)(txt, codepage is 1252, embedded labels, delimiter is '\t', msq) where Executed_Date>Last_Updated_Date;
Concatenate
File1:
LOAD [id__transaction,],
[Amount €]
FROM
(.....)\File1.QVD(QVD) where exists(id_transaction);
store table Fil1.QVD;
Drop table TableData;
Hi Jean,
Thanks for your reply.
I will be receiving these files every week and I have one more question with this. If i have some data common in file 1 and file-2. So I want to replace data of file-2 in file-1 which having common transaction dates.
Thanks,
Rajender.
Hi Bala,
Thanks for your reply.
I will be receiving these files every week and I have one more question with this. If i have some data common in file 1 and file-2. So I want to replace data of file-2 in file-1 which having common transaction dates.
Thanks,
Rajender.