Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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 subcall DoDir ('PathToQVDFiles')
Kind regards
BI Consultant
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;
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
BI Consultant
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
looked good to me but that is what I get
it creates dupicated field names with the file name in front
Hi,
It's due to your qualify *. Remove it and QV will concatenate your data.
Regards,
Vincent
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