topic Re: Storing multiple values in a variable from For loop in QlikView

Storing multiple values in a variable from For loop

rsdhavle — Sat, 16 Nov 2024 05:14:16 GMT

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?

Re: Storing multiple values in a variable from For loop

m_woolf — Thu, 15 Nov 2018 13:18:00 GMT

something like:
for i = 1 to 100

next i

Re: Storing multiple values in a variable from For loop

rsdhavle — Thu, 15 Nov 2018 13:19:46 GMT

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

Re: Storing multiple values in a variable from For loop

petter — Thu, 15 Nov 2018 13:26:00 GMT

In your loop you can do this:

v1 = v1 & ',' & v2;

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.

Re: Storing multiple values in a variable from For loop

jpenuliar — Thu, 15 Nov 2018 13:35:05 GMT

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

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

Re: Storing multiple values in a variable from For loop

petter — Thu, 15 Nov 2018 13:30:47 GMT

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

Re: Storing multiple values in a variable from For loop

rsdhavle — Thu, 15 Nov 2018 13:33:21 GMT

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?

Re: Storing multiple values in a variable from For loop

petter — Thu, 15 Nov 2018 13:33:31 GMT

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;

Re: Storing multiple values in a variable from For loop

rsdhavle — Thu, 15 Nov 2018 13:44:36 GMT

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..

Re: Storing multiple values in a variable from For loop

petter — Thu, 15 Nov 2018 15:41:26 GMT

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.

Re: Storing multiple values in a variable from For loop

petter — Fri, 16 Nov 2018 06:42:34 GMT

If you find any or all of the replies or suggestions helpful please mark them with a like as it seems to be the only way you credit anyone on this community site except from tagging with answered. Just writing "thanks" or anything verbally doesn't give people credit technically although they of course appreciate that too... 🙂

Re: Storing multiple values in a variable from For loop

rsdhavle — Fri, 16 Nov 2018 13:42:19 GMT

I have one question with this .i understand for loop will impact performance but this query gives me 100 values in multiple rows however when i pass this accounts values in the variable i will have to pass one value(consisting of 100 ) accounts at a time. So i will need loop right passing 1 row at a time?

Re: Storing multiple values in a variable from For loop

petter — Fri, 16 Nov 2018 13:44:13 GMT

Correct - so you will not avoid the for loop entirely but you speed up the generation of the variables enormously.