Create a loop with variable

[random_user_3869]

Hello QLik team,

I have the following variables 

I. 

LET vL.NomAppPrefixeNIV3 = UPPER(TEXT(Pick(wildmatch(DocumentTitle(),
'rec*','test*','niv3*','*suivi*Objectif*','*challenge*','*backlog*','*pilotage*commercial*','*achats*','*buca*')
,'rec_','test','niv3','Suiv','CHAL','BACK','Pilo','ACHA','BUCA')));

II.

LET vL.EnvironnementNIV3 = pick(Match('$(vL.NomAppPrefixeNIV3)','REC_','TEST','NIV3','SUIV','CHAL','BACK','PILO','ACHA','BUCA'),
'lib://Données AS400 - Rec:DataFiles','lib://Données AS400 - Test:DataFiles','lib://Données AS400:DataFiles','lib://Données AS400:DataFiles','lib://Données AS400:DataFiles',
'lib://Données AS400:DataFiles','lib://Données AS400:DataFiles','lib://Données AS400:DataFiles','lib://Données AS400:DataFiles'); //Environnement

Instead of adding values every time in each variables i would like to create a straight table (xls format with each values

First column variable and 2nd column values (for one variable there are N values).

I would like to create a statement like for i to n values

...

So that it can take into account each values

Anyone has an idea on how to do this ?

Thank you

[chrismarlow]

Hi,

I think the loop would look a bit like this;

data:
Load * Inline [
Match, Return
rec*, rec_
test*, test
];

Let vMatch='';
Let vReturn='';

for i=0 to NoOfRows('data')-1
	
	LET vMatch=vMatch&''''&peek('Match',i,'data')&''',';
	LET vReturn=vReturn&''''&peek('Return',i,'data')&''',';

next

LET vVar='UPPER(TEXT(Pick(wildmatch(DocumentTitle(),'&Left(vMatch,Len(vMatch)-1)&'),'&Left(vReturn,Len(vReturn)-1)&')';

You would want to point the load at the spreadsheet, but you can get that through the data connection wizards.

Cheers,

Chris.

[random_user_3869]

Thank you for the reply

can you explain to me this:

LET vMatch=vMatch&''''&peek('Match',i,'data')&''',';

I don't understand why you put vMatch at the begining if the value is 0

why put vMatch&''''& instead of ONLY peek('Match',i,'data)

Thank you

[chrismarlow]

Hi,

Because the next time the loop runs I want to bolt the next value on the end to build the bit of the string that will form the list to run the wildmatch against.

So i=0 I get 'rec*', (appended to the empty string), then when i=1 appends 'test*', giving 'rec*,'test*', (the final comma explains why we need the Left in vVar function).

Does that help?

Cheers,

Chris. 

[random_user_3869]

Hello,

Thank you again for your insight.

What i need to know is for i= 0 what is the result of this:

LET vMatch=vMatch&''''&peek('Match',i,'data')&''',';

AND this 

LET vReturn=vReturn&''''&peek('Return',i,'data')&''',';

Because as you put the following values

Let vMatch='';
Let vReturn='';  before the variable statement basically what i understand is for i = 0 

==> LET vMatch=vMatch&''''&peek('Match',i,'data')&''','; ==> LET vMatch=peek('Match',i,'data')&''',';

because Let vMatch='';

Basically i don't understand the use of saying LET vExample = vExample &peek(Field,i,Table). Can't you jsut put 

peek(Field,i,Table) ??

And about the following: LET vVar=UPPER(TEXT(Pick(wildmatch(DocumentTitle(),'&Left(vMatch,Len(vMatch)-1)&'),'&Left(vReturn,Len(vReturn)-1)&');

Basically for Len(vMatch)-1) means if len(text) =4 then len(text)-1 = 3 right ?

Thank you for your help 

[chrismarlow]

Hi,

If you just put peek() in without the vExample& then when it finishes running you are just going to get the last value in vExample (i.e. from the last row in the data), rather than all the values concatenated together (with quotes and commas, so works a a list).

If you can put debugging on maybe step through line by line, with something with vExample& and something without, and that will help?

The final point on len() - yes that is right - and the reason it is there is that otherwise there will be a trailing comma.

Cheers,

Chris.