text to columns but in a neat way

[#1]

Hello all,

I have a column of character data set which consist of codes of discounts we give our agencies. Also there are rows with empty data (no discount)

I would like to get a result as below without generating any additional row:

[#2]

Hi,

One way to do this is to create the different discount column as in your preferred output table and then give each of them a formula that tests if the column "packets" contains that code such as:

or in JMP script

Contains( Packets, "CT" );

This will give a 1 if test (does the Packet row contain "CT") and a 0 if it does not.

[#3 · Accepted solution]

If I was Julian I would make a cool video, however, this solution is easier than it appears so hang in there as I explain.

As is often the case, there is more than one way to solve your data cleaning.  The option suggested will work well if you do not have too many codes as you will have to manually set up each column.  However, if I were to use formulas in manually set up columns I would use the formula:

If( Contains( :Packet, "CT" ),

  "CT",

  Empty()

)

This would give you the structure you are looking for.  If you have lots of codes then another way would be to use the categorical platform (Analyze>Consumer Research>Categorical). 

You will need an ID column (I just added a new column and used the "initialize data" option under column info to create a column of unique numbers.  Next use the categorical response analysis, select the "Multiple" tab, select your packet column and then choose "Multiple Delimited" and use your ID column as the "X Grouping Category".  (see below)

[Missing image]

Now your output (which is just an intermediate step) will look like:

[Missing image]

Now under the main red triangle menu select Save Tables>Save Frequencies to get a table that looks like:

[Missing image]

Finally, if you need to you could use the column recode to change 1's to the codes and the 0's to blanks.  Also, you could join this back to the original table using the ID to match on.

[#4]

Thank you Karen,

Köszönöm Hegedus,

Even the solution of Hegedus is also ok, the help of Karen saved me a lot time hence the packets are coded in 67 different ways.

Many many thanks all!

[#5]

These are some good suggestions for solving this problem....

There is a really nice solution in JMP 12 and it almost made me cry.

In JMP 12 this is a menu item and it only takes about 5 clicks.

Cols/Utilities/Text to Columns, then add the delimiter, and check the "Indicator Columns" box. Click OK

[#6]

Really nice! But the data table is being updated every hours, I can not automate it by scripting, or can I? I guess not

[#7]

or maybe you can...

Straight from the scripting index:

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/Consumer Preferences.jmp" );

dt << Text To Columns(

  delimiter( "," ),

  columns( :Brush Delimited ),

  makeindicatorcolumns(1)//ok, so this line wasn't in the scripting index, but it was an intuitive guess

);

--Note: This is a new function in JMP12