3 Replies Latest reply: May 30, 2012 9:42 AM by Stefan Wühl RSS

    concatenated monthly qvd load and date tag

      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

        • concatenated monthly qvd load and date tag
          Stefan Wühl

          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);

            • concatenated monthly qvd load and date tag

              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...

                • concatenated monthly qvd load and date tag
                  Stefan Wühl

                  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.