Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load multiple QVD with different fields into one single table

Hello,

I have a folder with multiple QVD which are generated daily. Each file's name is MYDATA_[year][month][day].qvd. Each file contains the same number of fields and each field has the same name. Each file contains just one row. Example:

MYDATA_20120129.qvd

Data: 20120129

Field_A : Value_A

Field_B : Value_B

Field_C : Value_C

I introduce a change on Jan 30th, so the files have now a new field

MYDATA_20120130.qvd

Data: 20120130

Field_A : Value_A

Field_B : Value_B

Field_C : Value_C

Field_D : Value_D

So, when I try to load each file in a single QV, the Field_D cannot be found on each file, obviously, and the load fails.

LOAD *

FROM C:\MYDATA_*.qvd (qvd);

I try to add the "WHERE EXISTS(Field_D)" unsuccessfully.

I need to load files dated prior to January 30th with fields A, B, C and assigning default value 0 to field D; and load files dated after January 30th with fields A,B,C and D into one single table.

Anyone has an idea?

Thank you!

1 Solution

Accepted Solutions
Not applicable
Author

Finally, thanks to Henric, I found the solution:

LOAD * INLINE

    Field_A, Field_B, Field_C, Field_D

];

FOR EACH vFile in FileList('C:\MYDATA_*.qvd')

            CONCATENATE

            LOAD * FROM [$(vFile)] (qvd);

NEXT vFile

Thank you all!

View solution in original post

3 Replies
nstefaniuk
Creator III
Creator III

I understand that's the issue is the group load of n QVD using * in the filename, when all the QVD have not the same number of fields.

You can:

- do 2 steps of load, 1 for the dates prior to January 30th and 1 for dates after, but you can't use * in the file name so.

- do a loop on the files and concatenate the content, then you won't have error. You can fill a missing field with "NullAsValue 'FIELDNAME'; Set NullValue = 0;" before the load and "NullAsNull 'FIELDNAME';" after.

hic
Former Employee
Former Employee

The following script will assign NULL to missing values

Set vConcatenate = ;
for each vFile in FileList('MYDATA_*.qvd')
$(vConcatenate)
LOAD * FROM [$(vFile)] (qvd);
Set vConcatenate = Concatenate;
next vFile

If you really want zero in missing values instead, then you can do it by looping over dates instead. Wildcards in the filename works, but is not as flexible as a real for – next loop.

Not applicable
Author

Finally, thanks to Henric, I found the solution:

LOAD * INLINE

    Field_A, Field_B, Field_C, Field_D

];

FOR EACH vFile in FileList('C:\MYDATA_*.qvd')

            CONCATENATE

            LOAD * FROM [$(vFile)] (qvd);

NEXT vFile

Thank you all!