4 Replies Latest reply: Nov 24, 2012 11:56 AM by Axel Gustafsson RSS

    Generating varibles using FOR loop

    Axel Gustafsson

      Hi everyone,

      I want to create a number of variables that contains a list of Dim values. Both Client A and Client B DimId becomes the values of the varible. If I had created them manually it would have looked like this:

      Let Dim1ID = A1, B1;

      Let Dim2ID = A2,B2;

      ...

       

      I'm will do this with a FOR-loop that will generate eight variables.

      My data looks like this:

      Table:

      LOAD * INLINE [

          ClientId, DimId, Pos, RowCounter

          A, A1, 1, 1

          A, A2, 2, 2

          A, A3, 3, 3

          A, A4, 4, 4

          A, A5, 5, 5

          A, A6, 6, 6

          A, A7, 7, 7

          A, A8, 8, 8

          B, B1, 1, 9

          B, B2, 2, 10

          B, B3, 3, 11

          B, B4, 4, 12

          B, B5, 5, 13

          B, B6, 6, 14

          B, B7, 7, 15

          B, B8, 8, 16

      ];

       

       

      LET NoRow = Peek('RowCounter',-1,'Table');

       

      FOR b=1 to '$(RowCounter)'

      Let Dim$(b)ID = Peek('DimId',$(b),'Table'); //$(Client)

      NEXT

       

      I want to create these variables as a list, where both Client A and Client B DimId becomes the value of the varible. If I would have created it manually it would have looked like this:

      Let Dim1ID = A1, B1;

      Let Dim2ID = A2,B2;

      ...

       

      I'm not used to work with loops so I need som help.

        • Re: Generating varibles using FOR loop
          Gysbert Wassenaar

          Try this:

           

          LOAD * INLINE [

              ClientId, DimId, Pos, RowCounter

              A, A1, 1, 1

              A, A2, 2, 2

              ...

              B, B8, 8, 16

          ];

           

          Temp:

          load ClientId as ClientIdA,DimId as DimIdA, Pos Resident Table where ClientId = 'A';

          join (Temp) load ClientId as ClientIdB,DimId as DimIdB, Pos Resident Table where ClientId = 'B';

           

          LET NoRow = Peek('Pos',-1,'Temp');

           

          FOR b=1 to $(NoRow)

          Let Dim$(b)ID = Peek('DimIdA',$(b)-1,'Temp') & ',' & Peek('DimIdB',$(b)-1,'Temp') ;

          NEXT

           

          Drop Table Temp;

            • Re: Generating varibles using FOR loop
              Sujeet Panda

              Hi,

               

              I tried this but not able to load the table data.

               

              LOAD * INLINE [

                  ClientId, DimId, Pos, RowCounter

                  A, A1, 1, 1

                  A, A2, 2, 2

                  A, A3, 3, 3

                  A, A4, 4, 4

                  A, A5, 5, 5

                  A, A6, 6, 6

                  A, A7, 7, 7

                  A, A8, 8, 8

                  B, B1, 1, 9

                  B, B2, 2, 10

                  B, B3, 3, 11

                  B, B4, 4, 12

                  B, B5, 5, 13

                  B, B6, 6, 14

                  B, B7, 7, 15

                  B, B8, 8, 16

              ];

               

              Temp:

              load ClientId as ClientIdA,DimId as DimIdA, Pos Resident Table where ClientId = 'A';

              join (Temp) load ClientId as ClientIdB,DimId as DimIdB, Pos Resident Table where ClientId = 'B';

               

              LET NoRow = Peek('Pos',-1,'Temp');

               

              FOR b=1 to $(NoRow)

              Let Dim$(b)ID = Peek('DimIdA',$(b)-1,'Temp') & ',' & Peek('DimIdB',$(b)-1,'Temp') ;

              NEXT

               

              Drop Table Temp;err.png

              • Re: Generating varibles using FOR loop
                Axel Gustafsson

                Great!

                Thank you very much Gysbert!

                 

                What if I dont know how many Clients there are. If I want to add a client (without adding anything i the script)?

                 

                I belive I need to make a loop to create the expression you created...

                Let say I'm adding a third client.. but I want to use the same script.

                 

                How can I create that?

                 

                //Axel