Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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????
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);