Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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!

Tags (4)
1 Solution

Accepted Solutions
Not applicable

Re: Load multiple QVD with different fields into one single table

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!

3 Replies
nstefaniuk
Contributor III

Load multiple QVD with different fields into one single table

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.

Employee
Employee

Load multiple QVD with different fields into one single table

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

Re: Load multiple QVD with different fields into one single table

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!

Community Browser