topic Re: Data Load in QlikView script in QlikView

Data Load in QlikView script

rajender_qlik — Wed, 25 Nov 2020 16:16:04 GMT

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 !!

Re: Data Load in QlikView script

rupamjyotidas — Mon, 26 Jun 2017 19:39:25 GMT

Use Incremental Load

Concept below.

http://help.qlik.com/en-US/sense/1.1/Subsystems/Hub/Content/LoadData/UsingQVDFilesIncrementalLoad.htm

Basically where not exists and where exists will help you

Re: Data Load in QlikView script

sergio0592 — Mon, 26 Jun 2017 20:09:07 GMT

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,]);

Re: Data Load in QlikView script

BalaBhaskar_Qlik — Tue, 27 Jun 2017 06:54:26 GMT

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;

Re: Data Load in QlikView script

rajender_qlik — Tue, 27 Jun 2017 13:24:01 GMT

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.

Re: Data Load in QlikView script

rajender_qlik — Tue, 27 Jun 2017 13:24:23 GMT

Hi Bala,

Thanks for your reply.

Thanks,

Rajender.