concatenated monthly qvd load and date tag

[#1]

Hi,

I have salesdata in separate qvd files, with same headers, so I do a concatenate load, which works :

LOAD * From Report_2010.001.qvd(qvd);

CONCATENATE LOAD * From Report_2010.002.qvd(qvd);

CONCATENATE LOAD * From Report_2010.003.qvd(qvd);

CONCATENATE LOAD * From Report_2010.004.qvd(qvd);

CONCATENATE LOAD * From Report_2010.005.qvd(qvd);

CONCATENATE LOAD * From Report_2010.006.qvd(qvd);

CONCATENATE LOAD * From Report_2010.007.qvd(qvd);

CONCATENATE LOAD * From Report_2010.008.qvd(qvd);

CONCATENATE LOAD * From Report_2010.009.qvd(qvd);

CONCATENATE LOAD * From Report_2010.010.qvd(qvd);

CONCATENATE LOAD * From Report_2010.011.qvd(qvd);

CONCATENATE LOAD * From Report_2010.002.qvd(qvd);

CONCATENATE LOAD * From Report_2011.001.qvd(qvd);

CONCATENATE LOAD * From Report_2011.002.qvd(qvd);

CONCATENATE LOAD * From Report_2011.003.qvd(qvd);

CONCATENATE LOAD * From Report_2011.004.qvd(qvd);

CONCATENATE LOAD * From Report_2011.005.qvd(qvd);

CONCATENATE LOAD * From Report_2011.006.qvd(qvd);

CONCATENATE LOAD * From Report_2011.007.qvd(qvd);

CONCATENATE LOAD * From Report_2011.008.qvd(qvd);

CONCATENATE LOAD * From Report_2011.009.qvd(qvd);

CONCATENATE LOAD * From Report_2011.010.qvd(qvd);

CONCATENATE LOAD * From Report_2011.011.qvd(qvd);

CONCATENATE LOAD * From Report_2011.002.qvd(qvd);

CONCATENATE LOAD * From Report_2012.001.qvd(qvd);

CONCATENATE LOAD * From Report_2012.002.qvd(qvd);

CONCATENATE LOAD * From Report_2012.003.qvd(qvd);

CONCATENATE LOAD * From Report_2012.004.qvd(qvd);

Only problem is, because the date is in the QVD header, it is not as a column in my data.

So basically, I load all qvd's but the data concatenates into one table without date information.

How could I fix this?? I would like to make an additional field which somehow creates the date for every row per qvd....

thanks

[swuehl]

You could try using filename() and some string functions:

LOAD *,makedate(mid(filename(),8,4),mid(filename(),13,3))  as FileDateMonthStart From Report_2010.001.qvd(qvd);

[#3]

Hi Swuehl

When i do the following :

LOAD * From Report_2010.001.qvd(qvd);

LOAD *,makedate(mid(filename(),8,4),mid(filename(),13,3))  as FileDateMonthStart From Report_2010.001.qvd(qvd);

Just to test the first Qvd, then it fetches my 15.000 lines, but the script doesn't stop...

[swuehl]

No, I haven't intended that you are loading the data twice, use only one load per qvd, and always with the additional new field.

LOAD *,makedate(mid(filename(),8,4),mid(filename(),13,3))  as FileDateMonthStart From Report_2010.001.qvd(qvd);

If you do load the data twice, once with an additional field, you will get a huge syn key table (that's probably why it doesn't stop.)

You could also consider replacing your multiple loads with one, like

LOAD *,makedate(mid(filename(),8,4),mid(filename(),13,3))  as FileDateMonthStart From Report_201*.qvd(qvd);

which will load all qvds that match the pattern.