topic Common Script to Concatenate similar QVDs from same folder? in QlikView

Common Script to Concatenate similar QVDs from same folder?

dmohanty — Wed, 28 Jan 2015 01:12:54 GMT

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

Re: Common Script to Concatenate similar QVDs from same folder?

Anonymous — Wed, 28 Jan 2015 01:21:43 GMT

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

Re: Common Script to Concatenate similar QVDs from same folder?

dmohanty — Wed, 28 Jan 2015 01:32:06 GMT

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?

Re: Common Script to Concatenate similar QVDs from same folder?

Anonymous — Wed, 28 Jan 2015 13:50:43 GMT

Can you provide a more representative example?

Re: Common Script to Concatenate similar QVDs from same folder?

Anonymous — Wed, 28 Jan 2015 14:03:53 GMT

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

Re: Common Script to Concatenate similar QVDs from same folder?

jmmayoral3 — Wed, 28 Jan 2015 14:33:43 GMT

Is the structure of the file name always the same ?

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

Re: Common Script to Concatenate similar QVDs from same folder?

jmmayoral3 — Wed, 28 Jan 2015 16:15:23 GMT

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

Re: Common Script to Concatenate similar QVDs from same folder?

dmohanty — Wed, 28 Jan 2015 21:13:22 GMT

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!

Re: Common Script to Concatenate similar QVDs from same folder?

Anonymous — Wed, 28 Jan 2015 21:29:11 GMT

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)

Re: Common Script to Concatenate similar QVDs from same folder?

jmmayoral3 — Thu, 29 Jan 2015 08:17:59 GMT

Hi Michael .

you are right

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

Re: Common Script to Concatenate similar QVDs from same folder?

dmohanty — Fri, 06 Feb 2015 18:44:43 GMT

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

Re: Common Script to Concatenate similar QVDs from same folder?

jmmayoral3 — Mon, 09 Feb 2015 10:10:28 GMT

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