topic Re: Incremental Load using Single Excel File in QlikView

Incremental Load using Single Excel File

vardhancse — Wed, 05 Jul 2017 10:35:40 GMT

Hi,

Have one requirement, could any one please suggest any method to achieve:

Excel file:

1. At point of time one excel file

2. Will append new records

3. May delete old records

4. Will not have any date column

Requirement:

1. Load the excel file

2. Stamp date based on reload time

3. append new records if any without deleting old records even though deleted in excel

4. one single QVD

5. QVD should have all historic data with Date field included

Please find the attached excel and QVW for reference.

Re: Incremental Load using Single Excel File

vardhancse — Wed, 05 Jul 2017 12:09:21 GMT

Hi could any one please let me know the solution for my requirement.

Re: Incremental Load using Single Excel File

felipedl — Wed, 05 Jul 2017 13:00:47 GMT

Hi Sasi,

See the attached QVW for a solution.

Hope it helps,

Felipe.

Re: Incremental Load using Single Excel File

vardhancse — Wed, 05 Jul 2017 13:05:45 GMT

Hi thank your help.

I need to schedule the task in QMC, so every time I run its appending the existing records and the count is increasing.

could you please try to look into the same.

Re: Incremental Load using Single Excel File

felipedl — Wed, 05 Jul 2017 13:45:26 GMT

Open up the QMC and go to the 'Task' tab to schedule the app, according to the scheduled time.

Creating reload tasks ‒ Qlik Sense

You need first to import the app in the 'App' tab.

Importing apps ‒ Qlik Sense

Felipe.

Re: Incremental Load using Single Excel File

vardhancse — Wed, 05 Jul 2017 14:37:22 GMT

Yes I know how to create the task in QMC.

But issue is that using your code if I am reloading with out appending new records or delete existing records.

the total count of records are getting increased.

Re: Incremental Load using Single Excel File

felipedl — Wed, 05 Jul 2017 14:55:19 GMT

Sorry Sasi but i didn't understand what happened.

The test i'm doing here is appending the new lines to the QVD with the new reloadtime.

Can you explain it better?

Re: Incremental Load using Single Excel File

vardhancse — Wed, 05 Jul 2017 15:17:20 GMT

Without appending new records can please try to reload the app.

Re: Incremental Load using Single Excel File

Anonymous — Wed, 05 Jul 2017 16:39:52 GMT

Try this. It assumes EMP ID is unique.

Re: Incremental Load using Single Excel File

vardhancse — Wed, 05 Jul 2017 17:27:34 GMT

Hi thank you.

Need to stamp the date for all the records.

and more over if I delete any records from excel qvd is also deleting the files.

Re: Incremental Load using Single Excel File

Anonymous — Wed, 05 Jul 2017 18:05:08 GMT

Here you go. I added a date. This script wouldn't delete records off the QVD based on your Excel file changes.

It's just not built to do that. If you're losing records there is something else going on. Like it's not finding your QVD to begin with so it's doing a full reload. When it does a full reload it will create the QVD with whatever is in the Excel file.

So you'll probably want to step through it and make sure the IF statement I put in is 'catching'.

//Check if QVD exists; if not do full reload

if isnull(QvdCreateTime(EMP.qvd)) THEN

EMP:

LOAD [EMP ID],

[EMP NAME],

Designation,

Department,

Today() as ImportDate

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Store EMP into EMP.qvd;

Drop Table EMP; //Drop the initial load table after storing QVD

//if QVD does exists only add new records

ELSE

EMP:

LOAD [EMP ID],

[EMP NAME],

Designation,

Department,

ImportDate

FROM

EMP.qvd

(qvd);

Concatenate(EMP)

//Loading new base file into temp table

EMP_Temp1:

LOAD

[EMP ID],

[EMP NAME],

Designation,

Department,

Today() as ImportDate

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1)

where not exists([EMP ID]);

Store EMP into EMP.qvd;

Drop Table EMP;

ENDIF;

Let vEMPRecordsQVD = QvdNoOfRecords('EMP.qvd');//Fetch no of records from initial base QVD

Re: Incremental Load using Single Excel File

vardhancse — Thu, 06 Jul 2017 17:25:28 GMT

Once will try now thank you

Re: Incremental Load using Single Excel File

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

Hi I don't have a primary key/unique field.

In that case can please let me know how to identify new records and append to the historic data

Re: Incremental Load using Single Excel File

Anonymous — Mon, 10 Jul 2017 15:22:38 GMT

If no unique field then how can you tell what to add?

Normally if there is no single unique field the uniqueness comes from combining multiple fields.

For instance, if your uniqueness comes from EMP_ID and Department you would create a unique key by

something like EMP_ID & '|' & Department as MyUniqueKey.