topic Re: Incremental Load from Excel File in QlikView

Incremental Load from Excel File

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

Hi All,

I am trying to do incremental load from excel. I have below table for example. Initial Load, I have data till 15-Jan'17. Highlighted records have been added later. How do I merge the new record? I have created a sample logic for the same. But it seems its not working. Please advise!!!

Re: Incremental Load from Excel File

kamal_sanguri — Mon, 29 May 2017 10:11:40 GMT

Hi Gayatri,

Pls refer this qlikview doc. Let me know if you have any question.

Re: Incremental Load from Excel File

kamal_sanguri — Mon, 29 May 2017 10:13:36 GMT

When you run it for the first time, it will load data till 15th Jan and subsequent run will bring remaining data.

Re: Incremental Load from Excel File

prieper — Mon, 29 May 2017 10:23:33 GMT

Basically correct, however it is not clear, why you change the files.

Typically you would load the data.qvd, determine the last date, concatenate data from Excel beyond this date and store the new dataset into data.qvd.

Code might be:

Sales:
    LOAD * FROM Sales.QVD (QVD);
RecentUpdate:
Load Max([Date Modified]) as MaximumDate Resident Sales;
Let vLastUpdatedDate= Peek('MaximumDate', 0, Sales);

CONCATENATE (Sales) LOAD * FROM [Product.xlsx]
(ooxml, embedded labels, table is Sheet1)
where ([Date Modified])> $(vLastUpdatedDate);

STORE Sales INTO Sales.QVD (QVD);

Peter

Re: Incremental Load from Excel File

qlikview979 — Mon, 29 May 2017 10:26:30 GMT

Hi may be,

This is very full to you.

https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

Re: Incremental Load from Excel File

gayatri7 — Mon, 29 May 2017 10:29:14 GMT

Have gone through that article and tried implementing the same logic. But it is not working.

Re: Incremental Load from Excel File

qlikview979 — Mon, 29 May 2017 10:34:04 GMT

Hi,

i tried with your excel its working fine for me.

Regards

Re: Incremental Load from Excel File

its_anandrjs — Mon, 29 May 2017 10:34:45 GMT

Gayatri,

Check the attached file for incremental load and on there the initial load script is commented which is for one time if required uncomment those script.

Regards

Anand

Re: Incremental Load from Excel File

qlikview979 — Mon, 29 May 2017 10:35:45 GMT

I tried like below.

Sales:

LOAD [Product ID],

Item,

Sales,

[Store Name],

[Date Modified]

FROM

(ooxml, embedded labels, table is Sheet1);

store Sales into Incremental.qvd;

//Calculating recent modified date in Sales.qvd

RecentUpdate:

Load Max([Date Modified]) as MaximumDate Resident Sales;

Let vLastUpdatedDate= Date(Peek('MaximumDate', 0, Sales));

drop table Sales;

IncrementalSales:

LOAD [Product ID],

Item,

Sales,

[Store Name],

[Date Modified]

FROM

(ooxml, embedded labels, table is Sheet1)

where ([Date Modified])> $(vLastUpdatedDate);

store IncrementalSales into Incremental.qvd;

Re: Incremental Load from Excel File

gayatri7 — Mon, 29 May 2017 10:35:49 GMT

I just wanted to cross check whether it is only extracting the new record or not. I wanted to store in separate QVD and once verified .. I will concatenate with existing data.

But I am not getting any record in that step.

Re: Incremental Load from Excel File

vinieme12 — Mon, 29 May 2017 10:36:47 GMT

hope you are using a field that is a combination of ProductID& Date

Re: Incremental Load from Excel File

gayatri7 — Mon, 29 May 2017 10:37:08 GMT

are you getting any record s in Incremental.qvd. As I was not getting any record.

Re: Incremental Load from Excel File

qlikview979 — Mon, 29 May 2017 10:46:36 GMT

Yes,I am getting data

Re: Incremental Load from Excel File

qlikview979 — Mon, 29 May 2017 11:08:55 GMT

Hi Gayatri,

Follow this steps This is help full to you

First Paste added records in separate sheet what you highlighted in Yellow color.

1) Load the Excel in qlikview , then go for reload ,you will get dates from Aug-15 to Jan-17.

2) Next Create QVD and reload Once again, check the qvd path its stored or not.Once stored the QVD.

Sales:

LOAD [Product ID],

Item,

Sales,

[Store Name],

[Date Modified]

FROM

(ooxml, embedded labels, table is Sheet1);

store Sales into Incremental.qvd;

3) Take max date from date column by using

RecentUpdate:

Load

DAte(Max([Date Modified]) )as MaximumDate

Resident Sales;

4) Create Variable like

Let vLastUpdatedDate= Date(Peek('MaximumDate', 0, Sales));

Drop table Sales;

Then go for reload , now variable will show highest date (15-Jan17)

5) Now past the yellow color data in same sheet .

6)Load the Table once again then Write the Where statement like below

IncrementalSales:

LOAD [Product ID],

Item,

Sales,

[Store Name],

[Date Modified]

FROM

(ooxml, embedded labels, table is Sheet1)

where ([Date Modified])> $(vLastUpdatedDate);

Concatenate(IncrementalSales)

LOAD [Product ID],

Item,

Sales,

[Store Name],

[Date Modified]

FROM

Incremental.qvd;

7) Over ride the above stored qvd like this

store IncrementalSales into Incremental.qvd;

Regards

Mahesh