topic Re: Generating varibles using FOR loop in QlikView

Generating varibles using FOR loop

atafsson — Sat, 24 Nov 2012 12:19:42 GMT

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)

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 — Sat, 24 Nov 2012 12:41:41 GMT

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') ;

Drop Table Temp;

Re: Generating varibles using FOR loop

Sat, 24 Nov 2012 16:22:57 GMT

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') ;

Drop Table Temp;

Re: Generating varibles using FOR loop

Gysbert_Wassenaar — Sat, 24 Nov 2012 16:43:04 GMT

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

Table:

LOAD * INLINE [

ClientId, DimId, Pos, RowCounter

Re: Generating varibles using FOR loop

atafsson — Sat, 24 Nov 2012 16:56:08 GMT

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