14 Replies Latest reply: Sep 19, 2014 3:32 AM by Atsushi Saijo

# 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

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

Hi!

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

Pseudo-code:

Data:

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)',..)

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.

• ###### 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)&')'

• ###### 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.

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

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

Regards

ASHFAQ

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

Thanks. Concat, commend above was successful.

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

Hi can you close this thread.

Regards

ASHFAQ

• ###### 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.

• ###### 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?

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)',..)

Sorry maybe I should have used EXIST()

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

Hi,

You didn't mark any answer as correct

Regards

ASHFAQ

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

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)',..)

Match should work. Can you post your app.

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

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.