topic Re: Scheduling Incremental Load in QlikView

Re: Scheduling Incremental Load

Ahmed_Turnaround — Thu, 28 Nov 2019 11:49:44 GMT

Hi,

I have created an Incremental Load QVD but I have a few questions, each month a file will be placed into a folder, is there a way to tell QlikView to look for a new file, rather than hardcoding the file name such as below and changing it each month?

[E:\Qlikdata\Insight Pack\Targets\Targets Dec 19.xls]

In this case, Dec and 19 will change as each month progresses with the latter changing when the year progresses.

Secondly, is there a way to schedule these so that the Incremental Load is kicked off upon a new file placed in the source folder OR monthly? I think the latter can be done but not sure about the former?

My goal is to make this automated and have no user intervention.

Re: Scheduling Incremental Load

mr_janne — Thu, 28 Nov 2019 13:21:18 GMT

Hi!

Maybe something like this? It will search the most recent file:

For each vFileName in Filelist ('files_to_search*.qvd') Max_File: LOAD filetime('$(vFileName)') as FileTime, '$(vFileName)' as FileName autogenerate(1) ; Next right join(Max_File) load max(FileTime) as FileTime resident Max_File; let vMaxFile = peek ('FileName',0,'Max_File'); drop table Max_File; Data: load * from [$(vMaxFile)](QVD);

Re: Scheduling Incremental Load

Ahmed_Turnaround — Thu, 28 Nov 2019 13:24:33 GMT

Hi @mr_janne thank you for your reply.

Just so I understand, your code will be kicked off upon a new file being placed into my target folder, regardless of what the filename is?

What does the 'Files_to_search*.qvd') mean?

Thank you for your help.

Re: Scheduling Incremental Load

mr_janne — Thu, 28 Nov 2019 13:35:35 GMT

Replace it with the path and wildcard of your filename:

E:\Qlikdata\Insight Pack\Targets\Targets *.xls

It will loop all files that have "Targets" in their filename and load the most recent one.

Re: Scheduling Incremental Load

Ahmed_Turnaround — Thu, 28 Nov 2019 13:41:39 GMT

Hi @mr_janne I have some testing to do. I will get back to you soon.

Re: Scheduling Incremental Load

Ahmed_Turnaround — Mon, 27 Jan 2020 15:16:58 GMT

Hi @mr_janne ,

Picking this up again, sorry for the delay. Am I right in assuming that I don't need a concatenate load or anything like that with your code?

Re: Scheduling Incremental Load

Ahmed_Turnaround — Mon, 27 Jan 2020 15:30:42 GMT

Hi @mr_janne ,

Below is my existing code:

SalesTarget: //Load Sales Targets data from a previously created Sales Targets QVD. LOAD Year, Month, [Item], [Rep 1], [Rep 2], [Rep 3], [Rep 4], [Rep 5], [Rep 6], SalesTargetPeriod FROM [E:\Qlikdata\IP1 Insight Pack 1.4\Data - T\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, [Item], [Rep 1], [Rep 2], [Rep 3], [Rep 4], [Rep 5], [Rep 6], [Year] & [Month] as SalesTargetPeriod FROM [E:\Qlikdata\IP1 Insight Pack 1.4\Targets\Targets March 20.xls] //(biff, embedded labels, table is Sheet1$) WHERE SalesTargetPeriod > $(Latest_Target_Date); (biff, embedded labels, table is Sheet1$) WHERE [Year] & [Month] > $(Latest_Target_Date); //Concatenate to the pre-existing QVD. CONCATENATE LOAD Year, Month, [Item], [Rep 1], [Rep 2], [Rep 3], [Rep 4], [Rep 5], [Rep 6], SalesTargetPeriod FROM [E:\Qlikdata\IP1 Insight Pack 1.4\Data - TRN\TA.SalesTargets.qvd] (qvd); //Replace old QVD. Store Incremental into E:\Qlikdata\IP1 Insight Pack 1.4\Data - TRN\TA.SalesTargets.qvd(qvd); //Drop incremental table as we no longer need it. DROP TABLE Incremental

The bit I want to replace is this here:

//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, [Item], [Rep 1], [Rep 2], [Rep 3, [Rep 4], [Rep 5], [Rep 6], [Year] & [Month] as SalesTargetPeriod FROM [E:\Qlikdata\IP1 Insight Pack 1.4\Targets\Targets March 20.xls] //(biff, embedded labels, table is Sheet1$) WHERE SalesTargetPeriod > $(Latest_Target_Date); (biff, embedded labels, table is Sheet1$) WHERE [Year] & [Month] > $(Latest_Target_Date);

So instead of having to go into the QVD and change the month name in the File name, it will see that there is a new file and add the data from the new file, into the existing QVD.

I want there to be less user intervention here.

Is this possible?