topic Need One Column to be both a Character and Numeric Data Type in Discussions

Need One Column to be both a Character and Numeric Data Type

Ribs11380 — Sun, 11 Jun 2023 00:01:29 GMT

Hi Everyone-

I'm a fairly new user of JMP 17 Pro, and I am working on a project where I have one column of data that I need to have as both a Character Data Type AND as a Numeric Data Type. I need both columns as I want to 1) concat two different columns together (using Character) while at the same time having this same data as Numeric in a second column. Currently if I change the one column to Character I am able to successfully use the Concat function but then after I create this I go to change the column to Numeric my Concat column goes blank on me. Thank you for your help!

Re: Need One Column to be both a Character and Numeric Data Type

StarfruitBob — Tue, 23 May 2023 20:49:14 GMT

@Ribs11380

Could you post a mockup of what you have and what you want?

When concatenating columns, the inputs and outputs are a character type. If you wrap your concatenation in num( your concat formula here ), in the formula editor, if the resulting data is able to be converted into a number, it will be. If you force a character type to become a number via column properties, it will be erased because character types are not numbers.

Re: Need One Column to be both a Character and Numeric Data Type

Craige_Hales — Tue, 23 May 2023 22:31:07 GMT

@StarfruitBob points out the num(...) function will convert character data to numeric. There is also a char(...) function that converts numeric data to character.

You might want to use a formula column for the concatenation of two numeric columns. The formula column's formula might be

char(a) || char(b)

for the numeric columns a and b. You'll probably want to keep numeric data in numeric columns for further work. Converting it to character will cause platforms like fit y by x to produce the wrong report for numeric data.

Name is a character variable that will make logistic, contingency, or oneway plots depending on which axis it is used on, and the type of the other axis.

Fix Y by X produces the expected Bivariate for two numeric columns.

Re: Need One Column to be both a Character and Numeric Data Type

Ribs11380 — Wed, 24 May 2023 17:55:09 GMT

Thanks to you both for reaching out! I have attached a mock example of what I am trying to do. Basically I need both worksheet and Date to be available in both Numeric and Character so I can concat both together to get my desired result: Ex: 1_24May2023, 2_25May2023, etc. Under the Concat Worksheet and Date Column. I am ok with having additional columns with the same data as well so for instance having Character Worksheet having a new column created and it can be labelled Numeric Worksheet and vice versa with Numeric Date with creating a new column labelled Character Date. It seems so simple but I'm just able to do something as easy as Insert a New Column and then Paste the Column into a new column and rename.

Re: Need One Column to be both a Character and Numeric Data Type

vince_faller — Wed, 24 May 2023 18:39:23 GMT

This seems like an XY problem. What are you actually trying to do?

Re: Need One Column to be both a Character and Numeric Data Type

txnelson — Wed, 24 May 2023 18:59:34 GMT

You can concatenate together your Character Worksheet column with your Numeric Date column by using the following formula

:Character Worksheet || "_" || Format(
	:Numeric Date,
	"ddMonyyyy",
	9
)

or in using the Concat() function

Concat( :Character Worksheet,  "_",
	Format( :Numeric Date, "ddMonyyyy", 9 )
);

The Format() function returns the date as a character string

Re: Need One Column to be both a Character and Numeric Data Type

Craige_Hales — Wed, 24 May 2023 19:05:56 GMT

right-click the top of the new column, pick formula from the pop up menu. Add the formula to do what you need.

the || is the concatenation operator. There are a bunch of date formats you can choose from.

Right-click a column->Column Info is the way I usually re-discover the format names. I'd recommend making names that sort well for most purposes; probably put the year first, then month, day, and sheet number last. Unless you need all the sheet 1s to sort together.

the datetime formats are in a separate category.