topic Qualify and multiple qvd files in QlikView

Qualify and multiple qvd files

Mon, 09 Jul 2012 11:47:46 GMT

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

Re: Qualify and multiple qvd files

nilesh_gangurde — Mon, 09 Jul 2012 12:29:03 GMT

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

Re: Qualify and multiple qvd files

Mon, 09 Jul 2012 12:39:57 GMT

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

Re: Qualify and multiple qvd files

magavi_framsteg — Mon, 09 Jul 2012 14:16:54 GMT

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=true&q=dodir

// 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.

Re: Qualify and multiple qvd files

nagaiank — Mon, 09 Jul 2012 14:28:01 GMT

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;

Re: Qualify and multiple qvd files

magavi_framsteg — Mon, 09 Jul 2012 14:30:16 GMT

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.

Re: Qualify and multiple qvd files

vincent_ardiet — Mon, 09 Jul 2012 15:05:49 GMT

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

Re: Qualify and multiple qvd files

Mon, 09 Jul 2012 15:10:15 GMT

looked good to me but that is what I get

it creates dupicated field names with the file name in front

Re: Qualify and multiple qvd files

vincent_ardiet — Mon, 09 Jul 2012 15:35:31 GMT

Hi,

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

Regards,

Vincent

Re: Qualify and multiple qvd files

Tue, 10 Jul 2012 05:54:40 GMT

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

Re: Qualify and multiple qvd files

supix — Fri, 16 Jun 2023 05:55:00 GMT

Thanks for sharing.

Perhaps

load * from resident temptable;

should be

load * resident temptable;