Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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...
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.