Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
atsushi_saijo
Creator II
Creator II

How to convert a column into ('(value1)','(value2)',..)

Dear Gurus,

Would you possibly advise how to convert values in a column into the form ('(value1)','(value2)',......)?

Let's say there is unique values in a single column table;

Unique
a
b
c
d
e

I would like to create something like

SET vCOND=('a','b','c','d','e');

I would highly appreciarte for your input.

Atsushi

14 Replies
miikkaqlick
Partner - Creator II
Partner - Creator II

Hi!

If you always know number of rows then this is simple:

Pseudo-code:

Data:

Load

     Unique

From...

Let vCond = Chr(39)&peek('Data',Unique,0)&Chr(39)&Chr(39)&peek('Data',Unique,1)...

If you don't know number of rows, then you have to loop that:

Let vCond = vCond&Chr(39)&peek('Data',Unique,$(i))

Br,

Miikka

Climber Finland

Anonymous
Not applicable

in variable Expression define a variable vCond with the definition

='("' & concat(Unique,chr(39)&','&chr(39))&chr(39)&')'

ashfaq_haseeb
Champion III
Champion III

Assign this to your variable.

='('&Concat(Unique,chr(39)&','&chr(39))&chr(39)&')'

Regards

ASHFAQ

atsushi_saijo
Creator II
Creator II
Author

Hi, I tried loop but I could not make it.

The maximum number of rows comes NoOfRows(), so I set

Let vMax=NoOfRows('Unique')

For i=0 to $(vMax)

Let vCond = vCond&Chr(39)&peek('Data',$(i),'Unique');

next

Somehow it failed to result.

atsushi_saijo
Creator II
Creator II
Author

Thank you, CONCAT idea was excellent. I did not know.

Below finally worked.

set vCOND=Concat(chr(39)&A&chr(39),',');

This is for 'a','b','c','d','e'

I will add () in later part of script. Thank you.

atsushi_saijo
Creator II
Creator II
Author

Thanks. Concat, commend above was successful.

ashfaq_haseeb
Champion III
Champion III

Hi can you close this thread.

Regards

ASHFAQ

atsushi_saijo
Creator II
Creator II
Author

Hi sorry to interrupt again: above method did not work within the script.

Somehow I cannot pass the variable to the script execusion. I am testing more to make feedback official.

atsushi_saijo
Creator II
Creator II
Author

Would you happened to know if I should do this conversion, just to use this variable range in MATCH() function?

I am in need to make a loading script where:

WHERE MATCH(UNIQUE, (data)) >0

Maybe I do not need such thing but simply UNIQUE = (data column name)?