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

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

atsushi_saijo — Thu, 11 Sep 2014 13:24:19 GMT

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

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

miikkaqlick — Thu, 11 Sep 2014 13:29:59 GMT

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

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

Anonymous — Thu, 11 Sep 2014 13:37:31 GMT

in variable Expression define a variable vCond with the definition

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

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

ashfaq_haseeb — Thu, 11 Sep 2014 13:41:10 GMT

Assign this to your variable.

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

Regards

ASHFAQ

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

atsushi_saijo — Thu, 11 Sep 2014 14:41:24 GMT

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

Somehow it failed to result.

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

atsushi_saijo — Thu, 11 Sep 2014 14:43:22 GMT

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.

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

atsushi_saijo — Thu, 11 Sep 2014 14:43:43 GMT

Thanks. Concat, commend above was successful.

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

ashfaq_haseeb — Thu, 11 Sep 2014 14:56:36 GMT

Hi can you close this thread.

Regards

ASHFAQ

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

atsushi_saijo — Thu, 11 Sep 2014 14:58:02 GMT

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.

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

atsushi_saijo — Thu, 11 Sep 2014 15:04:52 GMT

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

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

atsushi_saijo — Thu, 11 Sep 2014 15:06:40 GMT

Sorry maybe I should have used EXIST()

I will close this thread.

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

ashfaq_haseeb — Thu, 11 Sep 2014 15:17:36 GMT

Hi,

You didn't mark any answer as correct

Regards

ASHFAQ

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

atsushi_saijo — Fri, 12 Sep 2014 06:57:39 GMT

Hi, I have had an issue to pass this value to MATCH(field, $(vCOND)) and I did not marked. It does display on the UI. Would you possibly advise why it cannot work in this? (or maybe my experiment was not correct?) I'd appreciate for any feedback. Atsushi

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

anbu1984 — Fri, 12 Sep 2014 08:41:55 GMT

Match should work. Can you post your app.

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

atsushi_saijo — Fri, 19 Sep 2014 07:32:19 GMT

Good morning, and I apologize for taking time. I attempted to inherit $() variables during script, but it did not succeeded. Besides, other developers has encountered same issue. Referred solution of CONCAT is only workable in the UI area (charts etc) but not within the script.

The solution in this specific case was to use in MATCH() during loading. The resolution was to use EXIST() in a condition close for a field.

TBL: LOAD

IF( EXISTS(Unique,A), 1) as _ID

RESIDENT .....

Atsushi