3 Replies Latest reply: Aug 5, 2011 3:30 AM by a.tamosevicius RSS

    'qvd' file generator depending on a query result

      Hello. I want to create a loader script which stores qvd files with postfixes depending on a query result. (For example

      if query result is 1,2,5,7 then file names are: file1.qvd, file2.qvd, file5.qvd, file7.qvd)

      What should I use for iteration to avoid files for non-existing numbers in query ("gaps in the row")?

      I would be grateful for working examples.

        • 'qvd' file generator depending on a query result

          Hi guy,

           

          here is an example  that I create to show you what you could do. You can use this script like this in a new document.

           

          my script :

           

          // First you declare your table

          table1:
          load * inline [
          val1, dep
          12 , aa 
          123, aa
          223, bb
          233, cc
          ];

           

           

          // you make a loop for each row of your table
          for i = 1 to NoOfRows('table1')
                 //  you can get the name of the field that you want
             let vField = FieldName(1,'table1');
                  // you can get the value of a field like this
             LET vfieldvalue = FieldValue('$(vField)',$(i));
                  //you can store like this with a concatenation of the value of your data and a generic string
             store * from table1 into test & $(vfieldvalue).qvd ;

          next i

           

          This example wil create 4 qvd named like this :

          test12.qvd, test123.qvd, test223.qvd, test233.qvd

           

          Hope that it will help you.

          • 'qvd' file generator depending on a query result

            Hi,

            this is a littlee Script based on a german Northwind DB:

             

                

            ODBC CONNECT TO Nordwind;

            Customer:
            SQL SELECT Firma as Company, //field is not null
                KundenCode,
                Land,
                Ort,
                Region
            FROM nordwind.kunden;

            Counter:
            Load num(count(Company)) as Number
            resident Customer;

            Let varNumberEnd = num(Peek('Number',-1,'Counter'));

            for a=0 to $(varNumberEnd)-1

            Let varCompany = Peek('Company',$(a),'Customer');

            [StoreCust$(varCompany)]:
            noconcatenate load *
            resident Customer
            where '$(varCompany)'=Company;

            store [StoreCust$(varCompany)] into 'StoreCust'&$(varCompany).qvd;
            drop table [StoreCust$(varCompany)];

            next

             

            Hope that helps.

            Regards

            Sabine