topic Re: Qlikview load latest 3 months in load script in QlikView

Qlikview load latest 3 months in load script

DestinedTale — Sat, 16 Nov 2024 01:37:53 GMT

Hi, currently i have a for loop that iterate all qvd in the variable vPath.
Would like to know if its possible to do something like loading path 3 months in the for loop instead of iterate all qvd to lessen load time? Suspect its not working because my qvd name is not by timestap so maybe something like get the substring of the month in the qvd.
Thanks.

Qvd:
A_2019-09.qvd
A_2019-10.qvd
A_2019-11.qvd
A_2019-12.qvd

Current code:
FOR Each FoundFile in filelist('$(vPath)_A_*.qvd')

New code:
Let vLast3Months = num(AddMonths(today(),-3));
FOR Each FoundFile in filelist('$(vPath)_A_$(vLast3Months).qvd')

Re: Qlikview load latest 3 months in load script

Anil_Babu_Samineni — Thu, 26 Dec 2019 10:24:00 GMT

Perhaps this way?

FOR Each FoundFile in filelist('$(vPath)_A_*.qvd')
TableName:
Load $(FoundFile) as FileNames,
Date(Date#(Mid($(FoundFile), 3, 7), 'YYYY-MM'), 'MM-YYYY') as MonthName
From '$(vPath)_A_*.qvd';
Next FoundFile

Then, Play around like

Let vMax = Peek('MonthName', -1, 'TableName');
Let vMax-3 = Peek('MonthName', -3, 'TableName');

FinalTable:
NoConcatenate
Load FileNames, MonthName Resident TableName Where MonthName>='$(vMax-3') and MonthName<='$(vMax');

Re: Qlikview load latest 3 months in load script

DestinedTale — Fri, 27 Dec 2019 05:27:03 GMT

hi, thanks.

The issue with this is it will load all qvds then load latest 3 months which increase load time.

Would like to load just latest 3 months qvd without iterating all qvds in the path.

Re: Qlikview load latest 3 months in load script

DestinedTale — Mon, 30 Dec 2019 01:35:38 GMT

any one know how to solve this? thanks.

Re: Qlikview load latest 3 months in load script

DestinedTale — Thu, 02 Jan 2020 04:13:11 GMT

anyone can help in improving the load performance?

Re: Qlikview load latest 3 months in load script

lorenzoconforti — Thu, 02 Jan 2020 13:12:21 GMT

Do something like this in your original script. You will only load the last three files

fileToLoad:
Load
'A_'&year(AddMonths(today(),-RowNo()))&'-'&num(month(AddMonths(today(),-RowNo())),'00') as fileNameToLoad
AutoGenerate 3;

for each fileName in FieldValueList('fileNameToLoad')
LOAD
Name,
Value
FROM [lib://ForEach/$(fileName).qvd]
(ooxml, embedded labels, table is Sheet1);
next fileName;

Re: Qlikview load latest 3 months in load script

Frank_Hartmann — Thu, 02 Jan 2020 13:36:44 GMT

what about this?

for each file in filelist('....\*.qvd') if keepchar(subfield('$(file)', '_', -1), '0123456789') >= year(now()-90)& num(Month(now()-90),'00') then LOAD Year, Data, FileName() as Filename FROM [$(file)]; end if next

Re: Qlikview load latest 3 months in load script

DestinedTale — Thu, 02 Apr 2020 12:26:21 GMT

Hi, this works well but only if 3 months are 30 days. What about months where they 29 days or 31 days? will it miss one or include additional month of data?