topic Re: Loading latest 4 weeks of data in QlikView

Loading latest 4 weeks of data

qlikviewforum — Sun, 10 Nov 2013 15:24:56 GMT

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

Re: Loading latest 4 weeks of data

Sun, 10 Nov 2013 15:38:18 GMT

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.

Re: Loading latest 4 weeks of data

Gysbert_Wassenaar — Sun, 10 Nov 2013 18:57:33 GMT

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;

Re: Loading latest 4 weeks of data

Mon, 11 Nov 2013 08:30:51 GMT

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

Re: Loading latest 4 weeks of data

Mon, 11 Nov 2013 13:55:50 GMT

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.

Re: Loading latest 4 weeks of data

qlikviewforum — Tue, 12 Nov 2013 12:36:17 GMT

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

Re: Loading latest 4 weeks of data

Gysbert_Wassenaar — Tue, 12 Nov 2013 17:22:19 GMT

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

Re: Loading latest 4 weeks of data

qlikviewforum — Wed, 13 Nov 2013 02:52:09 GMT

Cool I will check and let you know...

Re: Loading latest 4 weeks of data

qlikviewforum — Wed, 13 Nov 2013 03:10:14 GMT

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!

Re: Loading latest 4 weeks of data

Clever_Anjos — Wed, 13 Nov 2013 03:16:26 GMT

MakeWeekDate(

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

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

)

Re: Loading latest 4 weeks of data

qlikviewforum — Wed, 13 Nov 2013 06:21:58 GMT

Perfect Thanks!