Scheduling Incremental Load

[Ahmed_Turnaround]

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. 

[mr_janne]

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);

[Ahmed_Turnaround]

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. 

[mr_janne]

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.

[Ahmed_Turnaround]

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

[Ahmed_Turnaround]

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? 

[Ahmed_Turnaround]

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?