7 Replies Latest reply: Mar 25, 2011 4:31 AM by Iassen Ivanov RSS

    Loading QVD file

    Francis vd Grijn

      I have made some QVD files. Now i want to reload those file in an new QVW. I have the following QVD files:

      23-03-2011_File1

      24-03-2011_File2

      In those files i have 2 fields eg: Salesperson and Amount. In my load script i want to add 23-03 and 24-03 when loading the QVD files. Is this possible ?

       

        • Loading QVD file
          s j

          you can perform delta load of 23-3 and 24-3 to existing data. following is just a template.

          load

          *

          from

          23-3

          concatenate

          load

          *

          from historical where not exists(primarykey);

           

          • Loading QVD file
            Ralf Becher

            Hi Francis,

            try something like this:

             

            Result:
            LOAD date#('23-03-2011','DD-MM-YYYY') as Date, Salesperson, Amount
            FROM 23-03-2011_File1.qvd (qvd);

            LOAD date#('24-03-2011','DD-MM-YYYY') as Date, Salesperson, Amount
            FROM 24-03-2011_File1.qvd (qvd);


            - Ralf

              • Loading QVD file

                Just building on the top of Ralf here...

                Why not:

                 


                <pre>Result:
                LOAD date#(LEFT(FILENAME(),10),'DD-MM-YYYY') as Date, Salesperson, Amount
                FROM *_File1.qvd (qvd);


                 



                  • Loading QVD file
                    Ralf Becher

                    Right, but you should consider what is in your folder! It's sometimes better to loop over a file list... ;-)

                    - Ralf

                      • Loading QVD file

                        yes, yes, of course...i only wanted to make the point, that you don't need to add each file separately, because that requires you to change the script every time you get a new file.

                          • Loading QVD file
                            Steve Dark

                            Hi there,

                            A couple of observations. First up, another alternative to listing all source files or using a star is looping through each file in code. This allows you to add logic to decide whether you want to take the file or not. For example here I am loading from a folder of Excel files - but want to ignore the temp files created when a file is opened:

                            For each vFileName in filelist(vSourceFolder & vFileMask)
                            if index(vFileName, '~$') = 0 then // ~$ denotes an open file temp file
                            LOAD * FROM [$(vFileName)]
                            (biff, no labels, table is RT$);
                            end if
                            next

                            The other observation is that parsing the filename of the QVD and using that as a date will work fine, but it will cause a non optimized QVD load. It will be much more efficient to write the date into the QVD file at time of creation (it's obviously known as it's in the file name). For more information on Optimized vs. Non Optimized search the community - but just to let you know it can be up to 100x quicker!

                            Hope that helps,
                            Regards,
                            Steve