Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
rajender_qlik
Contributor

Data Load in QlikView script

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


5 Replies
rupamjyotidas
Valued Contributor

Re: Data Load in QlikView script

Use Incremental Load

Concept below.

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

Basically where not exists and where exists will help you

sergio0592
Valued Contributor II

Re: Data Load in QlikView script

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

balabhaskarqlik
Honored Contributor

Re: Data Load in QlikView script

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;

rajender_qlik
Contributor

Re: Data Load in QlikView script

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.

rajender_qlik
Contributor

Re: Data Load in QlikView script

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.