Getting table values into a variable

[Tuzlukov]

Hi,

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

A

B

C

...

Z

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 😞 

[jmartineze · Accepted solution]

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.

J.

[Surya]

Hi,

Do simply..

in variable overview > add variable > vVariable and value

=concat(fieldname,',');

i think this is enough...

[Tuzlukov]

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

[Surya]

YOU JUST TRY front end ...

in edit script using end and using "LET"

[marcus_sommer]

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

[Tuzlukov]

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.

[Tuzlukov]

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;