How to recode a column with a formula in JSL

rfeick

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);
);

 

Mark_Bailey

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"
	);
);