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!!!

[dmohanty]

Hey Jose,

I am coming back to you again at this question (as I didn't realized then), the structure of the File Name is not same always.

I am directly giving you some three names of QVDs here to understand better:

1. PURCH_ORDER_ITEM_FACT_Jan 2015 (similar for rest of months)   //// File_Type has to be PURCH_ORDER_ITEM_FACT

2. VENDOR_CONFIRM_FACT_Jan 2015 (similar for rest of months)   //// File_Type has to be VENDOR_CONFIRM_FACT

3. POH_FACT_Jan 2015 (similar for rest of months)  //// File_Type has to be POH_FACT

Now I believe the below script must be changed a bit to get the correct Field Names here:

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);

Could you please help more on this????

[jmmayoral3]

If you have always      _<Mon>' '<YYYY>       at the end of the file name, then you have always 9 characters and you can play with them.

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

                  Left(filebasename('$(File)'),len(filebasename('$(File)')-9) as File_Type,

                  Right(filebasename('$(File)'),8) as File_MonYY

From $(File) (QVD);