cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
RonSwanson
Level I

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!

4 REPLIES 4
uday_guntupalli
Level VIII

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

   );
Best
Uday
RonSwanson
Level I

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

 

pmroz
Super User

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.

uday_guntupalli
Level VIII

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. 

Best
Uday