Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
JAN. 24, Q&A with Qlik: Making the Move to Qlik Cloud Reporting: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
abhaskar1
Contributor II
Contributor II

last two months data from QVD with wildcard using

i want to reload last two months data from QVD, need to mention a condition at QVD path

by using resident load it is working please suggest me how to resolve this.

my date format is YYYYMMDD.

Adj_Temp:
LOAD
'' as AWTaxGL
AutoGenerate(0);
Concatenate(Adj_Temp)
Load *
FROM
$(vQVDfolder)/AdjustmentWaiver/AdjustmentWaiver*.qvd
(qvd)

 

 

Labels (1)
  • SaaS

1 Solution

Accepted Solutions
abhaskar1
Contributor II
Contributor II
Author

Now it's working fine 

Thanks to @rubenmarin @mikaelsc 

 

View solution in original post

9 Replies
rubenmarin

Hi, you can use a variable to store the initial date and use it on the where clause:

LEt vStartDate=Date(AddMonths(today(),-2),'YYYYMMDD');
...
LOAD * from... (qvd) where DateField>=$(vStartDate);

 

mikaelsc
Specialist
Specialist

the problem is that you're going to query all qvd's then... 

i'd try to generate all dates that you need to load (in a sort of calendar table) 

and using a loop to peek every date value, store it as variable, load qvd, next date,  etc

abhaskar1
Contributor II
Contributor II
Author

thanks for response rubenmarin.and mikaelsc

using * as wildcard match at qvd path , so it's not reloading

any other solution please.

rubenmarin

Hi, the use of a wildcard should allow the use of a where condition. We need more info of what you are trying to do and wich error you are getting.

abhaskar1
Contributor II
Contributor II
Author

Thanks for response Rubenmarin.

below is my qvd folder and script.

instead of loading all qvd files need to reload only last 60 days files from QVD folder in script at QVD path.

abhaskar1_0-1637654783771.png

 

mikaelsc
Specialist
Specialist

probably something like this... 

 

let vMinDate = num(today()-60); 

let vMaxDate = num(today()); 

tempCalendar: 

load 

date($(vMinDate)+iterno()-1,'YYYYMMDD') as tempDate

autogenerate(1) 

while $(vMinDate)+iterno()-1<=$(vMaxDate) ; 

 

let vNumberOfDates = noofrows('tempCalendar')-1; 

for i = 0 to $(vNumberOfDates)

let vDate = peek('tempDate',$(i),'tempCalendar'); 

 

Adj_Temp:
Load *
FROM
$(vQVDfolder)/AdjustmentWaiver/AdjustmentWaiver$(vDate).qvd
(qvd); 

next i

 

 

abhaskar1
Contributor II
Contributor II
Author

Thanks for response Mikaelsc.

getting below error while loading script

abhaskar1_0-1637657960760.png

 

rubenmarin

Hi, that's telling you that there is no qvd for that particular date, do you have a qvd for every day? If yes, check the path and qvd names.

You can add a check to confirm if a file exists before loading it:

IF IsNull(QvdCreateTime('$(vQVDfolder)/AdjustmentWaiver/AdjustmentWaiver$(vDate).qvd'))=0 THEN
  Adj_Temp:
  LOAD..

ENDIF

BTW @mikaelsc answer is a good one, but I think it can be simplified to:

FOR i = 0 to 60 // or 59

  LET vDate = date(Today()-$(i),'YYYYMMDD'); 
  IF IsNull(QvdCreateTime('$(vQVDfolder)/AdjustmentWaiver/AdjustmentWaiver_$(vDate).qvd'))=0 THEN
    Adj_Temp:
    Load *
    FROM
    [$(vQVDfolder)/AdjustmentWaiver/AdjustmentWaiver_$(vDate).qvd](qvd); 
  ENDIF
NEXT i

 

abhaskar1
Contributor II
Contributor II
Author

Now it's working fine 

Thanks to @rubenmarin @mikaelsc