Load mulitple similar tables in a loop

[#1]

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

[#2 · Accepted solution]

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

[#3]

Thats great thanks, exactly what I needed!