topic Re: Concatenate table values into a string variable in Discussions

Concatenate table values into a string variable

RonSwanson — Tue, 13 Nov 2018 15:49:03 GMT

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

uday_guntupalli — Tue, 13 Nov 2018 15:57:51 GMT

@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];
		  );

   );

Re: Concatenate table values into a string variable

RonSwanson — Tue, 13 Nov 2018 16:04:10 GMT

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

pmroz — Tue, 13 Nov 2018 18:10:12 GMT

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

uday_guntupalli — Tue, 13 Nov 2018 19:20:18 GMT

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