Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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);
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.
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.
Hi @mr_janne I have some testing to do. I will get back to you soon.
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?
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?