Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

atafsson
Contributor

Generating varibles using FOR loop

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.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Generating varibles using FOR loop

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;


talk is cheap, supply exceeds demand
4 Replies
MVP & Luminary
MVP & Luminary

Re: Generating varibles using FOR loop

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;


talk is cheap, supply exceeds demand
Not applicable

Re: Generating varibles using FOR loop

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

MVP & Luminary
MVP & Luminary

Re: Generating varibles using FOR loop

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

Table:

LOAD * INLINE [

    ClientId, DimId, Pos, RowCounter


talk is cheap, supply exceeds demand
atafsson
Contributor

Re: Generating varibles using FOR loop

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