topic Incremental Load not working in QlikView

Incremental Load not working

Ahmed_Turnaround — Thu, 17 Oct 2019 09:37:14 GMT

Hi can anyone help me with this? I am trying to do an incremental load, I followed the guide here, https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/ and adjusted it so that it can work with my QlikView application, my code is as follows:

SalesTarget: //Load Sales Targets data from a previously created Sales Targets QVD. LOAD Year, Month, ISBN, [AM Target], [DW Target], [JM Target], [BL Target], [JC Target], [IW Target], SalesTargetPeriod FROM [E:\Qlikdata\IP1 Insight Pack 1.4\Data - TRN\TA.SalesTargets.qvd] (qvd); //Find Latest Date in Year/Month so we know where to continue loading new targets from. Last_YearMonth: LOAD MAX([Year] & [Month]) as MaxSalesTargetPeriod Resident SalesTarget; //Store this date into a variable so that we can work with it. Let Latest_Target_Date = Peek('MaxSalesTargetPeriod',0,'Latest_Target_Date'); //Drop Table. Drop Table SalesTarget; //Load Incremental Data. Incremental: LOAD Year, Month, ISBN, [AM Target], [DW Target], [JM Target], [BL Target], [JC Target], [IW Target], [Year] & [Month] as SalesTargetPeriod FROM [E:\Qlikdata\IP1 Insight Pack 1.4\Targets\Targets October 19.xls] (biff, embedded labels, table is Sheet1$) WHERE SalesTargetPeriod > $(Latest_Target_Date); //Concatenate to the pre-existing QVD. CONCATENATE LOAD Year, Month, ISBN, [AM Target], [DW Target], [JM Target], [BL Target], [JC Target], [IW Target], SalesTargetPeriod FROM [E:\Qlikdata\IP1 Insight Pack 1.4\Data - TRN\TA.SalesTargets.qvd] (qvd); //Replace old QVD. Store Incremental into TA.SalesTargets.qvd(qvd);

However, when I run this, I get this error:

Field not found - <SalesTargetPeriod> Incremental: LOAD Year, Month, ISBN, [AM Target], [DW Target], [JM Target], [BL Target], [JC Target], [IW Target], [Year] & [Month] as SalesTargetPeriod FROM [E:\Qlikdata\IP1 Insight Pack 1.4\Targets\Targets October 19.xls] (biff, embedded labels, table is Sheet1$) WHERE SalesTargetPeriod > 201909

However, from what I can see, I have defined SalesTargetPeriod, so not sure why QlikView is saying it isn't found?

Any help is greatly appreciated.

Thank you.

Re: Incremental Load not working

Ahmed_Turnaround — Thu, 17 Oct 2019 09:35:29 GMT

I think I've fixed this:

I changed this code here:

(biff, embedded labels, table is Sheet1$) WHERE SalesTargetPeriod > $(Latest_Target_Date);

To this:

(biff, embedded labels, table is Sheet1$) WHERE [Year] & [Month] > $(Latest_Target_Date);

And this worked.

It seems to have dropped the file into the folder where the QVD lives, which isn't ideal, but I think this is an easy fix.

Re: Incremental Load not working

atoz1158 — Thu, 17 Oct 2019 09:37:46 GMT

You are getting that error because the field SalesTargetPeriod does not exist in the spreadsheet you will have to use the Year and Month fields.

Regards

Adrian

Re: Incremental Load not working

martinpohl — Thu, 17 Oct 2019 09:39:32 GMT

in the excel file, field SalesTargetPeriod is not existing, so you need to declare it by month and year.

Regards

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.

Re: Incremental Load not working

Ahmed_Turnaround — Thu, 17 Oct 2019 09:40:46 GMT

Thanks @atoz1158 and @martinpohl . I realised this shortly after I raised the case!

Doh!