topic Re: Getting table values into a variable in QlikView

Getting table values into a variable

Tuzlukov — Sat, 16 Nov 2024 20:09:15 GMT

Hi,

I have a column (ColumnName) in some table with values:

...

And i want to put all of the values into the variable (vValues), to get comma separated values in one string like this:

vValues = 'A,B,C,...,Z'

How can i do this? Tried 'concat' but unfortunately it does not work 😞

Re: Getting table values into a variable

jmartineze — Tue, 03 Sep 2019 10:37:01 GMT

Hello,

you should make a loop and use the function peek().

Somethinkg like this

for N=0 to NoOfRows('TableName')-1

let vValue$(N)=peek('fieldName',N,'TableName')

next;

and concat all values.

Re: Getting table values into a variable

Surya — Tue, 03 Sep 2019 10:57:34 GMT

Hi,

Do simply..

in variable overview > add variable > vVariable and value

=concat(fieldname,',');

i think this is enough...

Re: Getting table values into a variable

Tuzlukov — Tue, 03 Sep 2019 11:15:18 GMT

Looks good, but it does not work when i use it in script editor. This expression returns empty vValues:
LET vValues = Concat(ColumnName,',')

Re: Getting table values into a variable

Surya — Tue, 03 Sep 2019 11:54:44 GMT

YOU JUST TRY front end ...

in edit script using end and using "LET"

Re: Getting table values into a variable

marcus_sommer — Tue, 03 Sep 2019 12:13:39 GMT

You need an aggregation-load for it if you want to do it in the script, like:

t: load concat(Field) as ConcatField from source;

and the you could pick the value from the table, like:

LET vValues = peek(ConcatField,0, 't');

What is the aim for this list?

- Marcus

Re: Getting table values into a variable

Tuzlukov — Tue, 03 Sep 2019 13:10:22 GMT

Aim of the list is future list of column names for crosstable.

Finaly my request looks like:

CrossFields: LOAD ColumnName Resident tmp2; LET CrossFieldsList = Concat(ColumnName,',','CrossFields');

And after reloading data i see CrossFieldsList as empty variable.

Re: Getting table values into a variable

Tuzlukov — Thu, 05 Sep 2019 05:41:14 GMT

Looks like the loop is the better way to get the string. Thanks a lot!

I use this code:

FOR i = 0 TO FieldValueCount('ColumnName')-1
LET CrossFieldsList = IF(CrossFieldsList='','',CrossFieldsList & ',') & '[' & Peek('ColumnName',i,'CrossFields') & ']';
NEXT i;