Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
rsdhavle
Contributor II

Storing multiple values in a variable from For loop

I have one for loop for values i = A1 to A100 . I am using peek function and storing 1 value at a time in a variable V1 = A1

and then passing this variable into another table to retrieve certain values.

Instead of 1 value at a time of i need to pass 'i' values V1 = A1,A2,A3,A4..  ..etc in a variable V1 comma separated format how can i achieve that in this case?

Labels (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Storing multiple values in a variable from For loop

You can use the WHERE clause checking with the RecNo() function to get a range:

LOAD
  Concat(aField, ',' ,aField) AS FieldString
RESIDENT
  aTable
WHERE
  RecNo()>=1 AND RecNo()<=100;

 

Repeating this with a FOR is not efficient so I would rather use the GROUP BY to create all the concatenated strings in one load:

 

LOAD
  RangeNo,
  Concat(aField, ',' ,aField) AS FieldString
GROUP BY
  RangeNo;
LOAD
  Ceil(RecNo()/100) AS RangeNo,
  aField
RESIDENT
  aTable;

 

The resulting table will consists of rows of 100 concatenated values on each row.

View solution in original post

12 Replies
mwoolf
Honored Contributor II

Re: Storing multiple values in a variable from For loop

something like:
for i = 1 to 100

next i
rsdhavle
Contributor II

Re: Storing multiple values in a variable from For loop

this is the loop i already have but i am storing currently 1 value from this loop in variable like v1 = 1 instead of that can i save multiple values in comma separated format like v1 = 1,2,3

MVP
MVP

Re: Storing multiple values in a variable from For loop

In your loop you can do this:

 

v1 = v1 & ',' & v2;

or

v1 = v1 & ',' & Peek('aField', i+1 , 'aTable');

 

After the loop you will have the variable v1 containing all the v2 values in one single string with comma as separator between the values.

Partner
Partner

Re: Storing multiple values in a variable from For loop

Wont a Concat() function be handy with your requirement?
https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/Scripting/St...

 

example below:

 


table00:
Load
Concat(Values,',') as Values;
Load * Inline [
Values
A1
A2
A3
A4
A5
A6
A7
A8
A9
A10
];

LET v1 = Peek('Values',-1,'table00');

MVP
MVP

Re: Storing multiple values in a variable from For loop

You are right Concat() is much much better instead of a FOR loop with Peek(). 

rsdhavle
Contributor II

Re: Storing multiple values in a variable from For loop

Can you give one sample please..how can i judge range for example i have 1 to 10000 values for 'i' with concat how can i define range?
MVP
MVP

Re: Storing multiple values in a variable from For loop

A Concat() version of the code would look like this:

 

TEMP:
LOAD Concat(aField, ',' ,aField) AS FieldString RESIDENT aTable;

v1 = Peek('FieldString',0,'TEMP');

DROP TABLE TEMP;

rsdhavle
Contributor II

Re: Storing multiple values in a variable from For loop

Thanks i am checking this however where will i define range in this? for example out of 1000 values i have to load set of 100 first then 101-200 and so on..

MVP
MVP

Re: Storing multiple values in a variable from For loop

You can use the WHERE clause checking with the RecNo() function to get a range:

LOAD
  Concat(aField, ',' ,aField) AS FieldString
RESIDENT
  aTable
WHERE
  RecNo()>=1 AND RecNo()<=100;

 

Repeating this with a FOR is not efficient so I would rather use the GROUP BY to create all the concatenated strings in one load:

 

LOAD
  RangeNo,
  Concat(aField, ',' ,aField) AS FieldString
GROUP BY
  RangeNo;
LOAD
  Ceil(RecNo()/100) AS RangeNo,
  aField
RESIDENT
  aTable;

 

The resulting table will consists of rows of 100 concatenated values on each row.

View solution in original post