Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
DestinedTale
Contributor II
Contributor II

Qlikview load latest 3 months in load script

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

Labels (2)
7 Replies
Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
DestinedTale
Contributor II
Contributor II
Author

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.

DestinedTale
Contributor II
Contributor II
Author

any one know how to solve this? thanks.

DestinedTale
Contributor II
Contributor II
Author

anyone can help in improving the load performance? 

lorenzoconforti
Specialist II
Specialist II

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;

Frank_Hartmann
Master II
Master II

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

 

DestinedTale
Contributor II
Contributor II
Author

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?