247809_en-US

liliconcom

Conditional Concate Cell content from different rows

Hello,

 

Having one case as showing below.

I'd like to concatenate the different rows of "Description" for the same "Case#" into one combined cell which is aligning with "Case#"

e.g. C2 is concatenating of B2, B3, B4 which are all for "Case 1"

      C5 is concatenating of B5 which is for "Case 2"

      C6 is concatenating of B6, B7 which are all for "Case 3"

In the end, will delete redundant rows just leaving one row for one Case# with its descriptions in the 2nd column

 

The possible #of description rows varies for different "Case". There are few thousands of "Case", it's impossible to do it manually 

I'd like to seek some advice to do it in JSL if possible, thank you:) 

 

Re: Conditional Concate Cell content from different rows

Pin,

This is fairly easy to do interactively, the JSL is a bit cumbersome, though because the number of descriptions is unknown and needs to be accommodated in the code. To do this interactively:

1. Create a column with the case #s filled in if it already isn't (i.e., you don't want missing values in that column). THis can be done with the formula

   If( Row() == 1 | !Is Missing( oldColRef ),
      oldColRef,
      Lag( newColRef, 1 )
   ) 

   where oldColRef is a reference to the old case column and newColRef is referencing the column you are creating in the formula
2. Select the Description column and Cols>Utilities>Make Indicator Columns
3. Use Tables>Summary to summarize by the new case column. Select all the indicator columns and choose Max as the statistic. Set the statistic column format to column. You can unlink to the original table, but that's not necessary.
4. In the resulting table, select all the indicator column, go to Cols > Utilities > Combine Columns. Make sure to check the Selected Columns are Indicator Columns box.

The resulting table should be what you're looking for, I believe. Let me know if this works. Thanks to @brady_brady who showed a variation on this in his 10+ Things You Don’t Know About JMP® (EU 2018 424) Discovery talk. If you really, really, really, need the JSL code, I can come up with something. It likely won't make sense to a novice scripter, though.

Re: Conditional Concate Cell content from different rows

Hi Pin,

 

Here is JSL to do this. I've also another way to do it interactively, using tools that automate some of the processes Don describes. Let me know if you need those; they are in the "Data Table Tools" addin on the file exchange.

 

As a side note, step 1 of Don's process you can do by selecting all rows in the column, right-clicking, and selecting "Replace Missing with Previous Value". (Note that this menu option only appears when there are actually missing values, or empty strings, present in the column.)

 

Back to the script:

 

The script below assumes column 1 is named "col1", column 2 is named "col2" and creates a column called "Combined", so change these to your preference.

 

I've attached a table with the script enclosed; try it, I think it will do what you want.

 

Cheers,

Brady

 

Names Default To Here(1);

dt = :col1 << get data table();

r = (dt << get rows where(:col1 == "" ));
for(i=1, i<=nrow(r), i++, dt:col1[r[i]] = dt:col1[r[i]-1]);

dt << new column ("Combined", character);

r = 1;

while (r <= nrow(dt),
 set = dt << get rows where(:col1 == dt:col1[r]);
 dt: Combined[r] = concatitems((dt:col2 << get values)[set], " ");
 r += nrow(set);
);

Re: Conditional Concate Cell content from different rows

Much appreciate @brady_brady @DonMcCormack 

Yup, using the script, I managed to get it done easily.

More effort to learn JSL, hope can write something in the future by myself:)