topic Load Multiple QVDs in QlikView

Load Multiple QVDs

manojkulkarni — Thu, 14 Jan 2016 06:11:18 GMT

Good Morning Friends,

I have set of QVDs having data from same table, but column structure of qvd's are different. Few of QVDs has 10 columns & few has 15 columns. First 10 columns are same in both cases & later 5 columns being added to new data. For Historic data , structure didn't changed. Here my question is how to load all of them into a single QVD ?

Re: Load Multiple QVDs

jagan — Thu, 14 Jan 2016 06:19:11 GMT

HI,

Try like this

Data:

LOAD

Column1,

Column2,

Column15

Autogenerate 0;

Concatenate(Data)

LOAD

FROM *.qvd (qvd);

Note: Replace Column1, Column2, ..... Column15 with your actual column names.

If this is not working then use loop to read each and every file.

FOR EACH file in FileList('filepath\*.qvd');

Data:

LOAD

Column1,

Column2,

Column15

Autogenerate 0;

Concatenate(Data)
Load * ,

From $(file) (qvd);

Next

Regards,

Jagan.

Re: Load Multiple QVDs

manojkulkarni — Thu, 14 Jan 2016 06:28:38 GMT

Thanks Jagan. Is there a way to do same without mentioning column names ? In feature if somebody adds new column to source without the help of developer it should work.

BTW I will check the above code.

Re: Load Multiple QVDs

HirisH_V7 — Thu, 14 Jan 2016 06:29:26 GMT

Hi,

If your done with all QVD's Transformations then,

Test_QVD:
Load *
From ;

Store Test_QVD into [TEST\Final.QVD];

Hope this Helps,

Hirish

Re: Load Multiple QVDs

manojkulkarni — Thu, 14 Jan 2016 06:33:28 GMT

This will not work, since structure is different it will create 2 tables

Re: Load Multiple QVDs

PradeepReddy — Thu, 14 Jan 2016 06:35:53 GMT

try like this..

LOAD * FROM QVD_Path\*.qvd (qvd);

Re: Load Multiple QVDs

Anonymous — Thu, 14 Jan 2016 06:36:00 GMT

Why not concatenate them??

And use a common Flag to differentiate all the tables??

Re: Load Multiple QVDs

manojkulkarni — Thu, 14 Jan 2016 06:42:31 GMT

How to concatenate ? I don't know from which QVD the structure is going to change ? I am looking for generic solution.

Re: Load Multiple QVDs

jagan — Thu, 14 Jan 2016 06:47:40 GMT

Hi,

Try this

SET vIndex = 1;

FOR EACH file in FileList('filepath\*.qvd');

IF vIndex = 1 THEN

Data:

LOAD

FROM $(file) (qvd);

ELSE

Concatenate(Data)
Load * ,

From $(file) (qvd);

ENDIF

LET vIndex = vIndex + 1;

Next

Re: Load Multiple QVDs

Anonymous — Thu, 14 Jan 2016 06:49:30 GMT

As per you, Set of QVDs from same table. It seems you are creating multiple QVDs from same Table with different different conditions, Is it right??

Now Say, I am having a table XXX, and I am going to drive two tables from this which is: XXX1, XXX2

XXX1:

Load

'XXX1' as Flag

Resident XXX;

Concatenate(XXX1)

XXX2:

Load

'XXX2' as Flag

Resident XXX;

and so on...What would be the issue in this case??

Drop table XXX;

Re: Load Multiple QVDs

manojkulkarni — Thu, 14 Jan 2016 06:54:57 GMT

Hi Balraj,

Thanks for your reply.

I mean set of QVD's are coming from multiple tables. Multiple tables are generated to hold big data set, few of the tables structure got changed due to addition of columns but the old one's where data is already present remains same. That's why concatenate logic will not work directly.

Re: Load Multiple QVDs

PrashantSangle — Thu, 14 Jan 2016 06:55:03 GMT

Hi,

try below script.

Data:

Load *

From Temp\QVD1.qvd;

concatenate

Load *

From New\*.qvd;

Note : To avoid duplicate of 1 st QVD you can place it in different folder and other QVD in different folder.

If you kept QVD1 in same folder it include duplicates of 1st QVD.

Regards,

Re: Load Multiple QVDs

manojkulkarni — Thu, 14 Jan 2016 06:55:24 GMT

Thanks Jagan. It is working..

Great

Re: Load Multiple QVDs

PradeepReddy — Thu, 14 Jan 2016 07:01:09 GMT

you can try like this also...

Let vFolder_Path='D:\Qlik_199478';

// Reads all file names in a folder with the extension '.qvd'

sub ScanFolder(Root)

for each FoundFile in filelist( Root & '\*.QVD')

FileList:

LOAD * inline

[

SourceFile,

$(FoundFile),

];

next FoundFile

end sub

Call ScanFolder('$(vFolder_Path)') ;

let vNoOfQVD=NoOfRows('FileList');

Set vConcatenate = ;

//Reading the data from all QVD files

FOR var_i = 1 TO $(vNoOfQVD)

vSourceFile = trim(PEEK('SourceFile', var_i-1, 'FileList'));

Final:

$(vConcatenate)

load *,

Filebasename() as QvwFile

from $(vSourceFile)(qvd);

Set vConcatenate = Concatenate;

NEXT var_i;

drop table FileList;

Re: Load Multiple QVDs

manojkulkarni — Thu, 14 Jan 2016 07:09:31 GMT

thanks pradeep.

Re: Load Multiple QVDs

Kushal_Chawda — Thu, 14 Jan 2016 07:33:53 GMT

one more way

Data:

LOAD * Inline [

Temp ];

for Each vFile in FileList('D:\Kushal\Test\*.qvd');

Concatenate(Data)

LOAD *,

FileBaseName() as FileName

From $(vFile)(qvd);

NEXT vFile

DROP Field Temp;