Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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;
Oops forgot to paste one line that gives the first load a table name:
Table:
LOAD * INLINE [
ClientId, DimId, Pos, RowCounter
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