2 Replies Latest reply: Apr 25, 2012 5:25 AM by blackryu RSS

    Load mulitple similar tables in a loop

      Hi,

       

      I have a database that I am trying to load all data from. This is payroll software, and every period the program recreates the same 16 tables.

       

      For example, for this payroll period there is a table called REF01M_2012_01_Payslips (which refers to payroll account REF01, Year 2012, Period 01)

       

      There are around 60 tables named like this for payslip data, going back 5 years. I don't want to have to manully select each one of these table in the load, and then have to go back every month and add in the newly added payroll.

       

      In general programming terms, I understand that we could have a multi-dimensional loop which goes through all tables. The general format of the table names is REFXXM_YYYY_PP_Tablename, where XX in {01,02,03}, YYYY in {2007,2008,2009,2010,2011,2012} and PP in {01,02,03,04,05,06,07,08,09,10,11,12}. The tables themselves contain no datestamp, so I would need to include the variables in the load of the table

       

      Therefore I think a loop like this would work

       

      let XX = 01

          where XX <= 03

                let YYYY = 2007

                Where YYYY <= 2012

                     let PP = 01

                     where PP <=12

                          vTable = "REF"&XX&"M_"&YYYY&PP&"_"

                          Load *,

                          PP as [Period],

                          YYYY as [Year],

                          XX as [Account Ref];       

                          SQL Select * from vTable&"Payslips"

                          PP = PP+1

                     Loop

                 YYYY = YYYY+1

                Loop

           XX = XX +1

           Loop

       

      The problem is, I don't know how to write such a loop in the Qlikview script and my gut feeling is that this isn't the most effecent approach.

       

      Could any one offer any pointers?

       

      Thanks

        • Re: Load mulitple similar tables in a loop

          Hello:

           

          Following script would suit your requirement:

           

          For Each XX In '01','02','03'
              For YYYY = 2007 to 2012
                  For Each PP IN  '01','02','03', '04','05','06', '07','08','09', '10', '11', '12'  
                      Let vTable = 'REF'&$(XX)&'M_'&$(YYYY)&$(PP)&'_Payslips';
                      Trace $(vTable); // write the table name to log

                      $(vTable):           
                       SQL Select * from $(vTable);
                      
                       Store $(vTable) Into $(vTable).qvd (qvd);
                       Drop Table $(vTable);
                  Next
              Next
          Next

           

           

          Thanks,

          Narasimha K