Common Script to Concatenate similar QVDs from same folder?

[dmohanty]

Hi All.

I have a common folder, where I have multiple QVDs in this format:

Fact1_Jan14, Fact1_Feb14, Fact1_Mar14.......Fact1_Dec14.

Fact2_Jan14, Fact2_Feb14, Fact2_Mar14.......Fact2_Dec14.

Fact3_Jan14, Fact3_Feb14, Fact3_Mar14.......Fact3_Dec14.

Fact4_Jan14, Fact4_Feb14, Fact4_Mar14.......Fact4_Dec14.

I can concatenate each set of QVDs and store into respective QVDs (Fact1, Fact2, Fact3, Fact4) writing 4 scripts like below (This is for Fact 1 and similar for rest Facts):

FOR EACH vFile in FileList('$(vMonthQVDPath)\Fact1_*.qvd')         

Fact1:

LOAD *
FROM $(vFile)(qvd);
NEXT vFile ;
Store Fact1 into $(vMonthQVDPath)\Fact1.qvd;

drop Table Fact1;

But wanted a help, instead of writing different scripts for different facts, if we can have a common script which will recognize the similar QVDs and will concatenate into respective final QVDs (Fact1.qvd, Fact2.qvd, Fact3.qvd, Fact4.qvd)

Could someone please help here?

Regards!!!

[Anonymous]

This short script should work, if I got your requirement right:

For i = 1 to 4

Fact$(i):
LOAD * FROM $(vMonthQVDPath)\Fact$(i)_*.qvd;
Store Fact$(i) into $(vMonthQVDPath)\Fact$(i).qvd;
Drop Table Fact$(i);

next

[dmohanty]

Thank you Michael for quick help.

But here I have multiple sets (not 4) and also the name is not Fact* for all sets. They can be anything like XYZ, ABC, MNO etc etc. The above was just an example.

So, it seems impossible for me to use them as Fact$(i).

Any further help please?

[Anonymous]

Can you provide a more representative example?

[Anonymous]

You can use a wildcard to load all the qvd's in the folder that start with Fact1,  The code will below will concatenate them all into table Fact1

Fact1:

LOAD * FROM Fact1*.qvd(qvd);

[jmmayoral3]

Is the structure of the file name always the same ?

example: <XXX><n>_<Mon><YY>

[jmmayoral3 · Accepted solution]

Try this.

--------------------------------------

DIRECTORY $(vMonthQVDPath);

// Load all "qvd" existing files in a directory

  for each File in filelist ('*.qvd') // Reads all files in directory

       Table_filenames_Aux:   

       load distinct filebasename('$(File)') as File_name1,

                  Left(filebasename('$(File)'),index(filebasename('$(File)'),'_')-1) as File_Type,

                  mid(filebasename('$(File)'),index(filebasename('$(File)'),'_')+1) as File_MonYY

       From $(File) (QVD);

   next File;

FileType_List:

LOAD  concat(distinct chr(39)&File_Type&chr(39),',') as File_Type_List

RESIDENT Table_filenames_Aux;

LET vFileTypeList = peek('File_Type_List',0,'FileType_List');

Drop table Table_filenames_Aux, FileType_List;

For each vFileType in $(vFileTypeList)

     $(vFileType):

NoConcatenate

     LOAD *

     FROM $(vFileType)_*.qvd (qvd);

     Store $(vFileType) into $(vFileType).qvd (QVD);

     Drop  table $(vFileType);

next   

[dmohanty]

Hi Michael,

Sorry for the confusion. As requested, I am elaborating a bit more:

Set of Monthly QVDs (Some months may be missing as we may not have data for that month):

Table ABC is split into:  ABC_Jan14.qvd, ABC_Feb14.qvd, ABC_Mar14.......ABC_Dec14.qvd.

Table MNO is split into:  MNO_Jan14.qvd, MNO_Feb14, MNO_Mar14.......MNO_Dec14.

Table PQR is split into:  PQR_Jan14, PQR_Feb14, PQR_Mar14.......PQR_Dec14.qvd.

Table XYZ is split into:  XYZ_Jan14.qvd, XYZ_Feb14, XYZ_Mar14.......XYZ_Dec14.qvd.

......

and many more Fact tables.

I can concatenate the by writing script for individual table, citing the Table name (as shown in script in my original post).

Instead of writing multiple scripts, wanted if we can do all in a single script so that the respective QVDs are merged into single respective QVDs as ABC.qvd, MNO.qvd, PQR.qvd , XYZ.qvd.

Regards!

[Anonymous]

That's what jmmayoral2 assumed, and he provided a solution as well.  My previous script is a small part at the end, loop through the values ABC, MNO, etc.  The script before that is to extract these vales and create a variable, thus preparing for the loop.
(Small note: I don't see a need for NoConcatenate here because tables are dropped at the end of each cycle)

[jmmayoral3]

Hi Michael .

you are right

Nonconcatenate is not necessary. I did a test and I forgot to erase it.