Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewforum
Creator II
Creator II

Loading latest 4 weeks of data

I have a QVD's in the format QVDNAME.YYYY_WW.qvd. What I would like to do it to read all QVD's from a folder and sort by descending and load only latest 4 into my dashboard if the user wants to load latest 4 weeks of data(Which are in BOLD letters below). Could you please help me out for the same?

QVDNAME.2014_02.qvd

QVDNAME.2014_01.qvd

QVDNAME.2013_52.qvd

QVDNAME.2013_51.qvd

QVDNAME.2013_50.qvd

.

.

.

10 Replies
Not applicable

You can create the variable and for loop to take last 4 weeks from now onwards:

FOR i=0 to 3

     LET vDate = DATE(Today() - $(i)*7);

     LET vYrMonth = YEAR('$(vDate)') & '_' & WEEK ('$(vDate)');

     QVDNAME:

     LOAD * FROM QVDNAME.$(vYrMonth).qvd (qvd) ;

NEXT i

You can change the vDate variable to corresponding week calulcation.

Gysbert_Wassenaar

Maybe something like this:

For each vFile in Filelist ('D:\QVdata\temp\Test\*.qvd')  

  Files:

  Load

    '$(vFile)' as FileName,

    makeweekdate(mid(subfield('$(vFile)','\',-1) ,9,4),mid(subfield('$(vFile)','\',-1),14,2)) As FileDate

    AutoGenerate(1);

Next vFile  

Temp:

First 4

Load FileName as FName

resident Files

order by FileDate desc;

For i = 0 to 3

  vFile = peek('FName',$(i),'Temp')

  load * from '$(vFile)' (qvd);

next

drop table Files,Temp;



talk is cheap, supply exceeds demand
Not applicable

Hi,

I have an additional question: I would like to load only the latest x files (controlled by a variable) and delete any older QVDs. How can I achieve that?

Thanks, Regards

Jens

Not applicable

We can't delete the qvd's from qv script. But you can do with Batch script (.bat file) and execute the .bat file by using EXECUTE function at end of script.

qlikviewforum
Creator II
Creator II
Author

Sorry it didn't work may be formating issue. I need to check

Gysbert_Wassenaar

There were some errors in my script. I've fixed those now.


talk is cheap, supply exceeds demand
qlikviewforum
Creator II
Creator II
Author

Cool I will check and let you know...

qlikviewforum
Creator II
Creator II
Author

I think I need to change the formatting.My QVDNAME format it something like below. I mean exact size of the text.

RFC_MONTH.2013_45.qvd

Can you please help!

Clever_Anjos
Employee
Employee

MakeWeekDate(

SubField(SubField('$(vFile)','.',-2),'_',1),

SubField(SubField('$(vFile)','.',-2),'_',2)

)