topic Re: Creating a new column from 2 columns with a formula in Discussions

Creating a new column from 2 columns with a formula

Meda — Fri, 09 Jun 2023 18:07:51 GMT

Hi,

Can someone help me write a code in JMP to recreate Column 3 in the attached file.

Column 3 was derived from columns 1 and 2. Basically if Column 1 is "PM01" and Column 2 is "A01" or "B01" or "C01" or "D01" or "E01" or "F01", then Column 3 will be "Lot 1" and so on.

Best,

Meda

Re: Creating a new column from 2 columns with a formula

jthi — Wed, 22 Dec 2021 18:40:03 GMT

Not really sure I can understand the logic you have in xlsx file. What do you do when the numbers don't match?

You can get last characters of string for example with Right()

Right("PM12", 2); //12

Re: Creating a new column from 2 columns with a formula

Meda — Wed, 22 Dec 2021 18:42:50 GMT

I want to recreate Column 3 in jmp using columns 1 and 2

Re: Creating a new column from 2 columns with a formula

Meda — Wed, 22 Dec 2021 19:13:53 GMT

I used this formula and it works for Lot 1; but I don't know how to expand it for PM01 to PM12 and for Lot 2 and 3.

If(
	:Panel Member == "PM01" & :Well # == "A01" | :Panel Member == "PM01" & :Well # == "B01" | :Panel Member == "PM01" & :Well # == "C01" |
	:Panel Member == "PM01" & :Well # == "D01" | :Panel Member == "PM01" & :Well # == "E01" | :Panel Member == "PM01" & :Well # == "F01",
	"Lot 1",
	"TBD"
)

Re: Creating a new column from 2 columns with a formula

jthi — Wed, 22 Dec 2021 19:46:32 GMT

Based on this, I would say CC Lot# is determined only by Well#

Re: Creating a new column from 2 columns with a formula

Meda — Wed, 22 Dec 2021 19:49:19 GMT

Ok, can you then write a formula to recreate column 3 by well#

Re: Creating a new column from 2 columns with a formula

jthi — Wed, 22 Dec 2021 20:11:14 GMT

Attachment in previous post (Sheet1.jmp) has one idea for it

Re: Creating a new column from 2 columns with a formula

Meda — Wed, 22 Dec 2021 21:13:26 GMT

Ok I got it thank you so much!

Re: Creating a new column from 2 columns with a formula

frank_wang — Wed, 22 Dec 2021 23:50:01 GMT

Hi Meda

Pls refer to annex files and click '+' after column check formula.

Have a nice day.

Re: Creating a new column from 2 columns with a formula

arooqi — Tue, 28 Dec 2021 17:28:26 GMT

Since you have few levels of Lots, you can recode the data to a new column and JMP will create the formula for you.