Generating varibles using FOR loop

[atafsson]

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.

[Gysbert_Wassenaar · Accepted solution]

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;

[#3]

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;

[Gysbert_Wassenaar]

Oops forgot to paste one line that gives the first load a table name:

Table:

LOAD * INLINE [

    ClientId, DimId, Pos, RowCounter

[atafsson]

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