Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Qualify and multiple qvd files

Hi

I have montly data stored as individual .qvd files.

If I use

unqualify *;

MyTable:

LOAD * from *.qvd;

all data goes into the table MyTable.

If I use

qualify *;

unqualify '*IDX';

MyTable:

LOAD * from *.qvd;

I get multiple tables named MyTable-1, MyTable-2 etc.

How can I load the data files into the single MyTable table?

Juerg

1 Solution

Accepted Solutions
Not applicable
Author

Thanks for all replies.

To not use qualify does not meet my requirement.

Found the solution myself doing an unqualified temp load first

unqualify *;

temptable:

load * from MandatHonorar*.qvd;

qualify *;

unqualify '*IDX';

mytable:

load * from resident temptable;

drop table temptable;

Juerg

View solution in original post

10 Replies
nilesh_gangurde
Partner - Specialist
Partner - Specialist

Hi Juerge,

You can concate all the files into single table.

script is as follows:

MyTable:

load * from QvdName1.qvd;

Concatenate(MyTable)

Load * from QvdName2.qvd;

Concatenate(MyTable)

Load * from QvdName3.qvd;

Concatenate(MyTable)

Load * from QvdName4.qvd;

Do it for all the Qvds.

And you will get all tha data into single table.

Regards,

Nilesh Gangurde

Not applicable
Author

Hi Nilesh

As I get a new qvd every month I would need to extend the script every month?

No better way to do this?

Juerg

magavi_framsteg
Partner - Creator III
Partner - Creator III

Hi juerg.

One way of doing this is with a for loop.

If you search for DoDir in the QV help pages or in the QV Reference Manual you will find something like the below code. You will have to modify the code to suit your needs and thus the indentation was lost during copy/paste.

You can search the forums too: http://community.qlik.com/search.jspa?peopleEnabled=true&userID=&containerType=&container=&spotlight...

// list all QV related files on disk

sub DoDir (Root)

     for each File in filelist (Root&' \*.qvd')

          // You have to implement a check if the table already exists.

          // If it does not exist, create it as below, else concatenate to it.

          // You can do the check with NoOfRows(TableName)

          MyTable:

          Load * FROM '$(File)' (qvd);

     next File

end sub

call DoDir ('PathToQVDFiles')

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

nagaiank
Specialist III
Specialist III

You may try this script, which does not require monthly maintenance. However, you need consistent naming of your qvd files.

MyTable:

LOAD * Inline [

DummyFieldName

];

Concatenate LOAD * From QvdName*.qvd (qvd);

Drop Field DummyFieldName From MyTable;

magavi_framsteg
Partner - Creator III
Partner - Creator III

That is very good, I didn't think of that.

I always check if the table exists, then concatenate it, else create it.

Thx for the heads up.

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

vincent_ardiet
Specialist
Specialist

Hi,

And may be can you add the name of the file in your table if you want to know which month is loaded :

MyTable:

LOAD * Inline [

DummyFieldName

];

Concatenate

LOAD *,

     filebasename() as FileName

From QvdName*.qvd (qvd);

Drop Field DummyFieldName From MyTable;

Regards,

Vincent

Not applicable
Author

looked good to me but that is what I get

Capture.GIF

it creates dupicated field names with the file name in front

vincent_ardiet
Specialist
Specialist

Hi,

It's due to your qualify *. Remove it and QV will concatenate your data.

Regards,

Vincent

Not applicable
Author

Thanks for all replies.

To not use qualify does not meet my requirement.

Found the solution myself doing an unqualified temp load first

unqualify *;

temptable:

load * from MandatHonorar*.qvd;

qualify *;

unqualify '*IDX';

mytable:

load * from resident temptable;

drop table temptable;

Juerg