Load multiple QVD with different fields into one single table

[#1]

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!

[nstefaniuk]

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]

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.

[#4 · Accepted solution]

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!