topic Re: Incremental Load with multiple CSV files and single sheet in QlikView

Incremental Load with multiple CSV files and single sheet

vardhancse — Thu, 06 Jul 2017 10:58:22 GMT

Hi,

Have one requirement:

1. Load multiple CSV files appended with date e.g: 2017-03-28 EMP Data.csv [Done]

2. based on file name date column need to be extracted[Done]

2. All csv files will be one single folder[Done]

3. Load all csv files and concatenate to one single table[Done]

4. After loading the files, old csv files will be deleted [Pending]

4. daily new file will be placed and append new file records to existing records in QV table[Pending]

PFA sample csv files and qvw

Can any one please let me know the incremental load logic need to be applied here.

Re: Incremental Load with multiple CSV files and single sheet

vardhancse — Thu, 06 Jul 2017 12:51:05 GMT

stalwar1‌

could you please help me

Re: Incremental Load with multiple CSV files and single sheet

vardhancse — Thu, 06 Jul 2017 17:27:32 GMT

atkinsow‌

Can please let me know any alternative.

Tried some solutions but they are generating separate QVD for each csv file, but the ultimate output should generate one single QVD.

Even though if we delete the csv in share drive also QV should maintain historic data.

Re: Incremental Load with multiple CSV files and single sheet

Anonymous — Thu, 06 Jul 2017 17:41:09 GMT

It may not be the most efficient but this should work. Assumes EMPId is unique and only adds new records.

============================================================

if isnull(QvdCreateTime(EMP_Data)) then

//Full reload if QVD not exists

Let vFilePath = '..\*EMP Data.csv';

EMP_Data:

LOAD EMPId,

Name,

Location,

Date(Left(FileName(),10),'YYYYMMDD') as Date_ID

FROM

$(vFilePath)

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where not Exists(EMPId);

Store EMP_Data into EMP_Data.qvd;

ELSE

BaseQVD:

LOAD EMPId,

Name,

Location,

Date_ID

FROM

EMP_Data.qvd

(qvd);

Concatenate(BaseQVD)

EMP_Data:

LOAD EMPId,

Name,

Location,

Date(Left(FileName(),10),'YYYYMMDD') as Date_ID

FROM

$(vFilePath)

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where not Exists(EMPId);

Store EMP_Data into EMP_Data.qvd;

endif;

Re: Incremental Load with multiple CSV files and single sheet

vardhancse — Fri, 07 Jul 2017 03:06:48 GMT

Thank you.

In my original data there is no primary key, in that case can please let me know does above solution will work.

Re: Incremental Load with multiple CSV files and single sheet

vardhancse — Fri, 07 Jul 2017 03:16:36 GMT

Hi,

Tried your solution, its loading but when we delete any old csv files, then its even deleting in the QVD as well.

My ultimate goal is to load all the CSV files, even though we delete the files from share drive historic data should be maintained in QVD

Re: Incremental Load with multiple CSV files and single sheet

balar025 — Fri, 07 Jul 2017 06:23:22 GMT

Hi ,

Try this.

Let vFilePath = '1302030\*EMP Data.csv';

Let qvdExists = isNull(QvdCreateTime('1302030\EMP_Data.qvd'));

if $(qvdExists) < 0 then

Trace New file came.No Qvd is there;

EMP_Data:

LOAD EMPId,

Name,

Location,

Date(Left(FileName(),10),'YYYYMMDD') as Date_ID

FROM

$(vFilePath)

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

else

Trace New file came with existing qvd;

EMP_Data:

LOAD*

FROM

[1302030\EMP_Data.qvd]

(qvd);

EMP_Data:

LOAD EMPId,

Name,

Location,

Date(Left(FileName(),10),'YYYYMMDD') as Date_ID

FROM

$(vFilePath)

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

endif;

Store EMP_Data into 1302030\EMP_Data.qvd(qvd);

thanks

Ravi Balar

Re: Incremental Load with multiple CSV files and single sheet

vardhancse — Fri, 07 Jul 2017 06:35:36 GMT

Hi Ravi

Thank you. Tried your solution

If we delete the CSV files also QVD is having historic data, but issue is that

If we take count(EMPId) after every reload the count is getting increased.

Re: Incremental Load with multiple CSV files and single sheet

vardhancse — Fri, 07 Jul 2017 06:40:56 GMT

If are not placing new file and if we reload the app, last loaded records were being added to the previous month.

Re: Incremental Load with multiple CSV files and single sheet

balar025 — Fri, 07 Jul 2017 06:42:36 GMT

Hi Sasi,

If your emp_id is present in new file then it will take duplicate. for that you need to use where exists in else condition while loading csv files.

like where not exists(EMPId);

Re: Incremental Load with multiple CSV files and single sheet

vardhancse — Fri, 07 Jul 2017 06:51:50 GMT

Thank you used where condition and your script works fine, can please let me know any way we can achieve with our primary key

Re: Incremental Load with multiple CSV files and single sheet

balar025 — Fri, 07 Jul 2017 06:57:03 GMT

Hi Sasi,

you can take max date from qvd and check the condition. Like extracted date of files should be greater than max date from qvd.

For Example,

Let vFilePath = '1302030\*EMP Data.csv';

Let qvdExists = isNull(QvdCreateTime('1302030\EMP_Data.qvd'));

if $(qvdExists) < 0 then

Trace New file came.No Qvd is there;

EMP_Data:

LOAD EMPId,

Name,

Location,

Date(Left(FileName(),10),'YYYYMMDD') as Date_ID

FROM

$(vFilePath)

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

else

Trace New file came with existing qvd;

EMP_Data:

LOAD*

FROM

[1302030\EMP_Data.qvd]

(qvd);

MaxDateQvd:

Load Max(Date_ID) as MaxDate

Resident EMP_Data;

Let vMaxDate = Peek('MaxDate',0,'MaxDateQvd');

EMP_Data:

LOAD EMPId,

Name,

Location,

Date(Left(FileName(),10),'YYYYMMDD') as Date_ID

FROM

$(vFilePath)

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where Date(Left(FileName(),10),'YYYYMMDD')>$(vMaxDate);

Bold part are changes in code.

-Ravi Balar

Re: Incremental Load with multiple CSV files and single sheet

vardhancse — Fri, 07 Jul 2017 07:03:21 GMT

Thanks once will try now

Re: Incremental Load with multiple CSV files and single sheet

Anonymous — Fri, 07 Jul 2017 14:30:57 GMT

Sorry you're having trouble. My code would never just delete a QVD. There is no script in there that would delete a file. Worse thing any store command should do is create a QVD with no records in it.

Is that what is happening?

If so, the IF statement I had must not be 'catching'. I would check the file paths and no a debug to see what is happening.

If Ravi's code is working for you then go with that.

Re: Incremental Load with multiple CSV files and single sheet

vardhancse — Sun, 09 Jul 2017 10:33:21 GMT

Hi Ravi

With Date > condition unique records are not getting recognized

Re: Incremental Load with multiple CSV files and single sheet

balar025 — Mon, 10 Jul 2017 04:32:05 GMT

HI Sasi,

Can you please explain on Unique record? It is based on what? EMPId or EMPId&Date ?

Regards,

Ravi Balar