2 Replies Latest reply: Jan 6, 2014 10:16 AM by Oliver Annells RSS

    Looping (or something else perhaps)?

    Oliver Annells

      Hi

       

      On our database we have a sales achieve file which holds sales by month for the last 3 years.

       

      I have written a LOAD statement that pick these fields out and concatenates them into a single table eg.

       

      SalesHistoryByAcc:

      LOAD

          SAAACN & '-' & SAABRN as %Acc,

          SAAS01 as [Sales value],

          $(vYear) as year,

          dual('Jan',1) as month

      ;

      SQL

          SELECT SAAACN, SAABRN, SAAS01 FROM ....SAHACCPF;

       

      LOAD

          SAAACN & '-' & SAABRN as %Acc,

          SAAS02 as [Sales value],

          $(vYear) as year,

          dual('Feb',2) as month

      ;

      SQL

          SELECT SAAACN, SAABRN, SAAS02 FROM ....SAHACCPF;

       

      LOAD

          SAAACN & '-' & SAABRN as %Acc,

          SAAS03 as [Sales value],

          $(vYear) as year,

          dual('Mar',3) as month

      ;

      SQL

          SELECT SAAACN, SAABRN, SAAS03 FROM ....SAHACCPF;

       

      ...etc etc...

       

      I know you can use a For..Next loop in the script, but can I use it in a Field name ie "SAAS0" & n

       

      Thanks

      Oli

        • Re: Looping (or something else perhaps)?
          Sean (Hyunku) Cho

          Hi Oli,

           

          Yes, you can use it with Dollar Sign Expansion.

           

          i.e

           

          For i=1 to 9

          SalesHistoryByAcc:

          LOAD

              SAAACN & '-' & SAABRN as %Acc,

              SAAS0$(i) as [Sales value],

              $(vYear) as year,

              dual('Jan',1) as month

          ;

          SQL

              SELECT SAAACN, SAABRN, SAAS0$(i) FROM ....SAHACCPF;

           

          Next

           

          or

           

          For Each i in '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'

          SalesHistoryByAcc:

          LOAD

              SAAACN & '-' & SAABRN as %Acc,

              SAAS$(i) as [Sales value],

              $(vYear) as year,

              dual('Jan',1) as month

          ;

          SQL

              SELECT SAAACN, SAABRN, SAAS$(i) FROM ....SAHACCPF;

           

          Next i

           

          This will go through SAAS01, SAAS02, SAAS03 ... SAAS12.

           

          You may need to work on the year and month logic to make it generic.

          For Each vYear in  ....

               For Each i in '01' ... '12'

                    SalesHistoryByAcc:

                    LOAD

                        SAAACN & '-' & SAABRN as %Acc,

                        SAAS$(i) as [Sales value],

                        $(vYear) as year,

                        //dual('Jan',1) as month

                         if(i = '01',

                              dual('Jan',1),

                              if(i ='02',

                                   dual('Feb',2)

                              ...

                              )) as month,

                    Dual(Date('$(i)/01/$(vYear)','MMM'), num($(i))) as month2

                    ;

                    SQL

                        SELECT SAAACN, SAABRN, SAAS$(i) FROM ....SAHACCPF;

               Next i

          Next vYear

           

          Hopefully this is what you needed.

           

          Sean