83122_en-US

liliconcom

Concatenate table values into a string variable

I have a table of characters and want to produce a string variable of the concatenated values of each column to use as the argument of a function.

 

Let's say this is my table of data:

Column1 Column2

Item1       Value1

Item2       Value2

Item3       Value3

Item4       Value4

Item5       Value5

 . . .              . . .

 

The table will have 2 columns but any number of rows. The output I'm looking for is this:

strColumn1 = 'Item1','Item2','Item3','Item4','Item5'

strColumn2 = 'Value1','Value2','Value3','Value4','Value5'

 

Any help much appreciated!

Re: Concatenate table values into a string variable

@RonSwanson, 
       Just make sure the column name is spelled correctly. Preferrably delete the space in the name of the column. 

Re: Concatenate table values into a string variable

Looks like you're trying to create an SQL IN list.  Here's another approach:

dt = New Table( "Untitled 81", Add Rows( 2 ),
 New Column( "Column 1", Character, "Nominal", Set Values( {"Item1", "Item2"} ) ),
 New Column( "Column 2", Character, "Nominal", Set Values( {"Value1", "Value2"} ) )
);
col1_list = column(dt, "Column 1") << get values;
col2_list = column(dt, "Column 2") << get values;
strcolumn1 = "('" || concat items(col1_list, "','") || "')";
strcolumn2 = "('" || concat items(col2_list, "','") || "')";

If any of the columns are numeric then the list returned from get values will be a matrix, and you will need to loop over the matrix to convert all elements to strings.

Re: Concatenate table values into a string variable

The Col1Vals command worked, however, got the following error when using the Col2Vals command:

 

Col2Vals = dt:Column 2 << get Values;

Scoped data table access requires a data table column or variable in access or evaluation of 'dt:Column 2' , dt:Column 2

In the following script, error marked by /*###*/

Col2Vals = dt:Column 2 << get Values

 

Re: Concatenate table values into a string variable

@RonSwanson,
         Maybe something like this 

 

dt = Current Data Table(); 

Col1Vals = dt:Column 1 << get Values; 
Col2Vals = dt:Column 2 << get Values; 

// If you are specific about how the output looks, you can try the following, else 
// the above lines should give you what you want 
Str1 = "";
Str2 = ""; 

for(i = 1, i <= N Items(Col1Vals), i++,
  If(i == 1,
    Str1 = Str1 || Col1Vals[i];
    Str2 = Str2 || Col2Vals[i];
    ,
    //else 
    Str1 = Str1 || "," || Col1Vals[i];
    Str2 = Str2 ||  "," ||Col2Vals[i];
    );

   );