topic Re: how to enter both text (character) and numeric (continuous) data in the same coloumn in JMP 13.2 in Discussions

how to enter both text (character) and numeric (continuous) data in the same coloumn in JMP 13.2

abdul_saqib — Thu, 23 Nov 2017 12:02:41 GMT

Hi,

In one of the Data Table columns in JMP, I want to have both, text (e.g, WAITING) and numbers in different rows of the same column. However, when I try to write text in a cell it says that data type is not correct.

Is there a straightforward solution please? (straightforward = using clicks, not programming).

Thanks

Re: how to enter both text (character) and numeric (continuous) data in the same coloumn in JMP 13.2

Craige_Hales — Thu, 23 Nov 2017 12:54:56 GMT

Yes, make the column be a character column, which can hold character strings that look like numbers.

Right-click in the column header->column properties, change Numeric to Character.

Numbers represented as character strings may not behave the way you expect. Sorting, in particular, makes "9.2" > "1000.0" because the first character, "9" is bigger than "1".

Re: how to enter both text (character) and numeric (continuous) data in the same coloumn in JMP 13.2

abdul_saqib — Thu, 23 Nov 2017 13:12:01 GMT

Thanks Craig.

Changing the column property to Text resulted in the loss of 'Row marking by column' (the color assigned to different rows). Whereas, I want to assign colour to rows based on both the numbers and the text in the reference column.

Thanks

Re: how to enter both text (character) and numeric (continuous) data in the same coloumn in JMP 13.2

txnelson — Thu, 23 Nov 2017 15:50:31 GMT

Rather than changing the column to a character modeleing type, you can specify the column property "Value Labels" to have specific falues displayed as a character string. You can either specify specific values to be displayed as specifc character strings, or you can specify ranges of values to be displayed. Any numeric values not covered by the defined values in the Value Labels are displayed as their original values.

Re: how to enter both text (character) and numeric (continuous) data in the same coloumn in JMP 13.2

Jeff_Perkinson — Thu, 23 Nov 2017 21:47:55 GMT

As you've discovered, a column in JMP is either Numeric or Character.

You said that you'd have "WAITING" or a number. Do you want to take an average of these numbers? What "number" should "WAITING" contribute to this average?

Perhaps you can explain what this data represents and how you'd like to analyze it, and we can help provide some more direction.

Re: how to enter both text (character) and numeric (continuous) data in the same coloumn in JMP 13.2

abdul_saqib — Tue, 28 Nov 2017 14:15:00 GMT

Thanks for the suggestion, but didn't seem to make it work. Please see the attachment where I added WAITING = WAITING rule and still could not enter the text WAITING in the continuous column.

Got the following message when I entered WAITING in a cell of the ‘Continuous’ column:

Re: how to enter both text (character) and numeric (continuous) data in the same coloumn in JMP 13.2

abdul_saqib — Fri, 24 Nov 2017 14:15:55 GMT

Good suggestion Jeff.

I used 0 instead of WAITING which made it possible to color rows based on this. Also, an additional column can be created to convert 0 back into WAITING using the logical 'if' formula. Thanks

Re: how to enter both text (character) and numeric (continuous) data in the same coloumn in JMP 13.2

txnelson — Fri, 24 Nov 2017 14:27:48 GMT

You may want to explore using a Missing Value, ".", instead of a zero. It can also be used in a logical IF clause, and as a value in Value Labels, and it has the added advantage of automatically being excluded when performing calculations on the column.

Re: how to enter both text (character) and numeric (continuous) data in the same coloumn in JMP 13.2

abdul_saqib — Tue, 28 Nov 2017 14:09:42 GMT

Hi Jim,

Tried, but it did not work as I intended; although I am not sure if I used it correctly or not (picture enclosed).

Thanks anyway. I guess, for now I will go witht he other solution to assign a number to text.

Re: how to enter both text (character) and numeric (continuous) data in the same coloumn in JMP 13.2

txnelson — Fri, 24 Nov 2017 16:21:36 GMT

You have to use an Is Missing() function to test for missing values

If(isMissing(Criteria) == 1, "GREEN",
Criteria<40, "RED",
"Orange"
);

Re: how to enter both text (character) and numeric (continuous) data in the same coloumn in JMP 13.2

Mark_Bailey — Fri, 24 Nov 2017 18:57:44 GMT

Enter a period (".") character (without double quotation marks) as the value to represent missing and "WAITING" for the label.

The values now appear as text in the data column (left justified) but they still had the numeric data type and the continuous modeling type.

Re: how to enter both text (character) and numeric (continuous) data in the same coloumn in JMP 13.2

abdul_saqib — Tue, 28 Nov 2017 12:31:54 GMT

It worked. Thansk Mark.

Re: how to enter both text (character) and numeric (continuous) data in the same coloumn in JMP 13.2

ih — Tue, 28 Nov 2017 13:24:15 GMT

Also, the numeric missing would be entered without quotes, for example:

myval = 1;
Is missing( myval ); //returns 0

myval = .;
Is missing( myval ); //returns 1

Re: how to enter both text (character) and numeric (continuous) data in the same coloumn in JMP 13.2

abdul_saqib — Fri, 01 Dec 2017 17:44:04 GMT

Another related issue has arisen meanwhile.

I changed the missing values (.) to 0 in a JMP data table, using the Value Label feature under Column Properties (Thanks to Jim for orignally bringing this feature to my attention and to Mark for for further clarifying it in the discussion above). The value label (0) seems to work as the numerical data and is shown up when the data is plotted in Graph Builder (see the top figure in the enclosed document). However, as soon as another factor is assigned to the Y-axis, the value labels data points disappear (the bottom figure in the enclosed document)!

I wonder if there is a solution to this issue please. Thanks

All results appear in the scatter plot with ONLY X - axis factor plotted:

As soon as another factor is placed on the Y - axis the value label data (green dots) disappear!! Note that the green dots have been assigned 0 value – the same value as for the 6 red dots at the bottom:

Re: how to enter both text (character) and numeric (continuous) data in the same coloumn in JMP 13.2

Mark_Bailey — Fri, 01 Dec 2017 13:38:33 GMT

I think that you want to "eat your cake and have it, too." Either the value is unknown (missing) or it is known (not missing). Using a value label was meant as a short cut in the data column. The JMP platforms, such as Graph Builder, generally work with the values, not the labels. An observation will not (should not) be included in a linear regression, for example, if its response or predictor value is missing, regardless of any assigned label.

You might re-think your approach and see if the label is actually necessary in the first place. Does it make sense to plot something that is unknown? (I can understand if you want to analyze the number of missing values but not individual observations.)

Re: how to enter both text (character) and numeric (continuous) data in the same coloumn in JMP 13.2

abdul_saqib — Fri, 01 Dec 2017 14:01:33 GMT

Yes probably that's the case! :)

Thanks for the explaination, Mark.