topic Re: How to recode a column with a formula in JSL in Discussions

How to recode a column with a formula in JSL

rfeick — Thu, 05 Jan 2017 02:21:44 GMT

I am having trouble getting my recode script to run correctly. I have a data table with a column called DEPT which contains a number which corresponds to a specific departent name. What I want to do is create a new column called Dept Name that takes the values in DEPT and converts them to the written department name rather than the numeric version. This table is part of a larger script that I run twice a week with a rolling year of data each time it's run so every department number/name may not be present in any given iteration. Below is what I've tried, but I've only suceeded in either simply transposing the value from DEPT into Dept Name or just getting a blank Dept Name column. Any help would be much appreciated.

FD << Current Data Table();
New Column("Dept Name", Character, Nominal, Formula(:DEPT));
:Dept Name << Delete Formula;
For Each Row(:Dept Name =
 Match( :Dept Name,
		"100", "Wet Production",
		"200", "QA Lab",
		"300", "Dry Component",
		"400", "Finished Component",
		"410", "Microsis",
		"415", "Antimicrobial",
		"425", "Finished Component Product",
		"475", "Special Processing",
		"490", "eSIS",
		"500", "QC",
		"600", "Labeling",
		"700", "Sterilization",
		"1000", "Shipping and Finished Product Labeling",
		"1200", "Quarentine",
		:Dept Name);
);

Re: How to recode a column with a formula in JSL

Mark_Bailey — Thu, 05 Jan 2017 12:22:04 GMT

I think that you are on the right track but your original script was confusing the original and the target data columns. Try this modification:

FD = Current Data Table();
FD << New Column( "Dept Name", Character, Nominal );
For Each Row(
	:Dept Name = Match( :Dept,
		"100", "Wet Production",
		"200", "QA Lab",
		"300", "Dry Component",
		"400", "Finished Component",
		"410", "Microsis",
		"415", "Antimicrobial",
		"425", "Finished Component Product",
		"475", "Special Processing",
		"490", "eSIS",
		"500", "QC",
		"600", "Labeling",
		"700", "Sterilization",
		"1000", "Shipping and Finished Product Labeling",
		"1200", "Quarentine"
	);
);

Re: How to recode a column with a formula in JSL

rfeick — Thu, 05 Jan 2017 13:57:14 GMT

That worked perfectly! Thanks for the help!

Re: How to recode a column with a formula in JSL

EPenny — Thu, 22 Sep 2022 15:16:22 GMT

Hello!

I'm attempting something similar here on JMP 16. The only difference, I believe, is that I have multiple values in each of my groups. This is the formula I'm trying now:

FD = Current Data Table();
FD << New Column( "Dog Group", Character, Nominal );
For Each Row( Dog Group = Match( :Breed #, "6,10,18,20,24,27,30,31", "Toy", "8,9,15,17,21,28,36", "Work", "1,14,23,26,29,33", "Sport", "3,4,7,13,34,37,42", "Hound", "11,12,16,39", "Herder", "5,19,41", "Extra Fluff", "32,40", "Terrier", "2,25", "Doodle", "22", "Mixed", "35,38", "Other" ) );

I also originally recoded with a formula and this works in my current data table - but I am not able to copy/paste the formula into a new table (even when making sure the column names are the same).

Thanks for any help!

Re: How to recode a column with a formula in JSL

jthi — Thu, 22 Sep 2022 15:43:42 GMT

I think you cannot do comparison like that when using Match, you would have to write all the values separately. I would use either Associative Array or data table to store the Breed # and groups (you could also use Match, but I think the other options are better). Depending on where you are getting those values there are many different ways of building these.

Re: How to recode a column with a formula in JSL

txnelson — Thu, 22 Sep 2022 16:06:33 GMT

If you want to use the Match paradigm it would have to be structured like:

FD = Current Data Table();
FD << New Column( "Dog Group", Character, Nominal );
For Each Row(
	Dog Group = Match( :Breed #,
		"6", "Toy",
		"10", "Toy",
		"18", "Toy",
		"20", "Toy",
		"24", "Toy",
		"27", "Toy",
		"30", "Toy",
		"31", "Toy",
		"8", "Work",
		"9", "Work",
		"15", "Work",
		"17", "Work",
		"21", "Work",
		"28", "Work",
		"36", "Work",
		"1", "Sport",
		"14", "Sport",
		"23", "Sport",
		"26", "Sport",
		"29", "Sport",
		"33", "Sport"
	)
);