Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: Qualify and multiple qvd files

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

9 Replies
nilesh_gangurde
Valued Contributor

Re: Qualify and multiple qvd files

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

Re: Qualify and multiple qvd files

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
Contributor III

Re: Qualify and multiple qvd files

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
Valued Contributor III

Re: Qualify and multiple qvd files

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
Contributor III

Re: Qualify and multiple qvd files

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
Contributor III

Re: Qualify and multiple qvd files

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

Re: Qualify and multiple qvd files

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
Contributor III

Re: Qualify and multiple qvd files

Hi,

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

Regards,

Vincent

Not applicable

Re: Qualify and multiple qvd files

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