Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

atsushi_saijo
Not applicable

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

Tags (1)
14 Replies
miikkaqlick
Not applicable

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

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

hrlinder
Not applicable

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

in variable Expression define a variable vCond with the definition

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

ashfaq_haseeb
Not applicable

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

Assign this to your variable.

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

Regards

ASHFAQ

atsushi_saijo
Not applicable

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

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
Not applicable

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

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
Not applicable

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

Thanks. Concat, commend above was successful.

ashfaq_haseeb
Not applicable

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

Hi can you close this thread.

Regards

ASHFAQ

atsushi_saijo
Not applicable

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

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
Not applicable

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

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)?