Pick the last 6 months QVDs from folder

[dmohanty]

Hi All,

In the QVD folder I have FACT_TABLE QVDs for each months and for last 2 Years.

For example -

FACT_TABLE_Dec 2013, FACT_TABLE_Jan 2014 ....FACT_TABLE_Dec 2014, FACT_TABLE_Jan 2015, FACT_TABLE_Feb 2015 ....FACT_TABLE_Nov 2015.

I have a script in QVD generator which was picking up all the similar QVDs for all months and combining into a single QVD - which is like this below -

FOR EACH vFile in FileList('$(vRootFileLocation)$(vQVDs)$(vSnapshotQVDPath)FACT_TABLE_*.qvd')


FACT_TABLE:

LOAD *
FROM $(vFile)(qvd);

NEXT vFile ;


STORE FACT_TABLE into '$(vRootFileLocation)$(vQVDs) FACT_TABLE.qvd';

DROP Table FACT_TABLE;

Now Requirement -

1. My QVD Generator should pick ONLY the last 6 months of QVD and combine into a single one. Rest all are not needed.
2. If the Old ones can be purged out by some script at the same time, its more good too.

Please help in this regard.

[#2]

You may need

CONCATENATE LOAD *

To add monthly data together.

Thanks.

[dmohanty]

Hi dust0000‌,

I am able to concatenate all the QVDs using the above mentioned script.

But now I wanted to concatenate the last 6 Months QVD inside/from that folder.

[#4]

You can reference this thread to get last 6 month qvd.

Load qvd by timestamp in script

[Anonymous]

load all qvd's as field values and create a conditions based on date and concatenate the last six months qvd as per condition.

  Load

         '$(vFile)' as FileName

      From [$(vFile)];

   Next vFile

[MarcoWedel]

Hi,

one solution could be:

loaded files:

present files.

using this script:

FACT_TABLE:
LOAD * FROM [QlikCommunity_Thread_192875_FACT_TABLE_*.qvd](qvd)
Where Date#(SubField(FileBaseName(),'_',-1),'MMM YYYY')>MonthStart(Today(),-6);

(although this script reads ALL files to find relevant records)

hope this helps

regards

Marco

[rwunderlich · Accepted solution]

I would use a simple FOR loop.

FOR i = 0 to -5 STEP -1  // How many months

  LET vMonth=Date(addmonths(today(2),$(i)),'MMM YYYY');

  LOAD * FROM [FACT_TABLE_$(vMonth).QVD] (qvd);

NEXT i

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[#8]

Marco has very smart solution. Here is my qvw with for loop.

Hope it help.

Thanks.

[arixooo123]

Hi Dicky,

Did you find a solution to your problem?

I have the same issue but instead of the last 6 files, I only want to pick the last file.

[dmohanty]

Hi arixooo123,

Try using this script to get the latest QVD in the path -

/*********************************
Script to Check the
Latest QVD in the path
*********************************/


For each vFile in Filelist ('YourPath\YourQVD_*.qvd')

Files:
LOAD
'$(vFile)' as FileName,
filetime('$(vFile)') as FileTime
autogenerate 1
;

Next vFile;


Tab:
LOAD MaxString(FileName) as LatestFile
Resident Files;
LET vLatestFile = Peek('LatestFile', 0, 'Tab');
DROP Table Files;