topic Re: Formula for combining columns? in Discussions

Formula for combining columns?

saitcopuroglu — Thu, 14 Jun 2018 14:03:40 GMT

I would like to combine 2 numeric column data with a delimiter.

First I used cancat function with a third concat character but it seems the columns to be character. I used the char function but the result is obvious if you look at Column "Column".

Column Utilities "Combine Colums" make the thing perfectly but it does not implement the formula inside the column properties so no automation.

I have this script from the help menu but i can not adapt it as a formula and outside jsl option does not help me becuse of full automation.

Really appreciate any help

Combine Columns(
    delimiter( "," ),
    Columns(
        :Brush After Waking Up,
        :Brush After Meal,
        :Brush Before Sleep,
        :Brush Another Time
    ),
    Selected Columns are Indicator Columns( 1 ),
    Column Name( "When to Brush" )
)

Re: Formula for combining columns?

pmroz — Thu, 14 Jun 2018 14:04:29 GMT

Click on Cols > New Column

Name the column When to Brush

Click Column Properties > Formula

Double-click the red box that says "no formula"

Paste the following code into the code window:

char(:Brush After Waking Up) || "," ||
char(:Brush After Meal) || "," ||
char(:Brush Before Sleep) || "," ||
char(:Brush Another Time)

Click OK

Re: Formula for combining columns?

saitcopuroglu — Tue, 18 Oct 2016 23:30:14 GMT

Thank you PMRoz,

I am fine with it but I can't do my version where the data is numeric (date/time):

Here is the result when I

Char(:PUBLISH DATE.) || "," || Char(:REVIEW NUMBER)

Re: Formula for combining columns?

pmroz — Thu, 14 Jun 2018 14:05:00 GMT

For dates use the format command, with your favorite JMP date or datetime format. Here's an example:

Format(:Publish Date, "m/d/y") || "," || Char(:Review Number)

Re: Formula for combining columns?

ENTHU — Thu, 25 Oct 2018 17:50:45 GMT

Sorry about multiple questions.Still new to jsl and in process of getting used to.

I have 2 columns in a data table - process and location. process has values like welding,melting,binding and preparing.Location has values like 29,15,6,22.

I need to write this data to an excel as welding@29,melting@15,binding@6,preparing@22.

My approach -

1.Combine columns process and location with delimiter @.

2.Get list of unique values using Associative array and get keys.

But the problem I am facing is -

There are instances where one or both columns can be blank.

If process is blank,the result is NA@23.If both columns are blank result is '.'.

How do I check the list for these values and remove them?

Thanks