Subscribe Bookmark RSS Feed

How to recode a column with a formula in JSL

rfeick

Community Trekker

Joined:

Sep 30, 2016

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
markbailey

Staff

Joined:

Jun 23, 2011

Solution

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"
	);
);
Learn it once, use it forever!
2 REPLIES
markbailey

Staff

Joined:

Jun 23, 2011

Solution

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"
	);
);
Learn it once, use it forever!
rfeick

Community Trekker

Joined:

Sep 30, 2016

That worked perfectly! Thanks for the help!