Create Categorical Variable Using Two Timepoints

sagrim

If anyone can help with this I would greatly appreciate it !

 

I am trying to create a categorical variable that will reflect changes in employment based on two variables: JOBCODE TimePoint 2 and JOBCODE TimePoint 2.

In essence, this categorical variable will allow me to see whether any given employee has one of the following events from timepoint 1 to timepoint 2: joined the organization (new employee), changed JOBCODE (transferred within the organization), or left the organization (termination/separation).    

The code below represents my data structure is because any new employees will have a blank/ null value for their JOBCODE at Timepoint 1, any terminated employees will have a blank/ null value for JOBCODE at Timepoint 2 and any employees who changed positions will have a non-match JOBCODE in the field from Timepoint 1 to Timepoint 2.

As far as the command for blank/null goes, I have tried a number of different things.  I tried using isMissing as well as == “” and I have also tried !contains.  None of them work.  I don’t have a preference on which one of these styles is used as long as it works. 

For those employees who transfer, I am only interested in whether or not they kept a job code contained in mylist

I created a mock data table.  Column 1 is some made up employee IDs for the sake of example.  Column 2 and 3 represent jobcodes at Timepoint 1 and Timepoint 2.  Column 4 is the output I would expect based on the values I entered in Column 2 & 3.  Column 5 is some notes for clarification. 

 

new column ("JOBCODE Status", Character, Nominal) << Formula

(If (Contains (mylist, :"JOBCODE Timepoint1") & !Contains (mylist, :"JOBCODE Timepoint2"),"Change in JOBCODE",

(If (Contains (mylist, :"JOBCODE Timepoint1") & Contains (mylist, :"JOBCODE Timepoint2"),"No Change",

(If (Contains (mylist, :"JOBCODE Timepoint1") & isMissing (:"JOBCODE Timepoint2"),

"Termination/Separation",

(If (isMissing (mylist, :"JOBCODE Timepoint1") & Contains (:"JOBCODE Timepoint2"),

"New Employee",



))))))));



mylist= {"HH0029",

"HH0030",&nbsp;&nbsp;&nbsp;

"HH0241",

"HH0242",&nbsp;&nbsp;&nbsp;

"HH0317",&nbsp;&nbsp;&nbsp;

"HH0635",&nbsp;&nbsp;&nbsp;

"HH0635",&nbsp;&nbsp;&nbsp;

"HH0636",&nbsp;&nbsp;&nbsp;

"HH0636",&nbsp;&nbsp;&nbsp;

"HH0639",&nbsp;&nbsp;&nbsp;

"HH0742",&nbsp;&nbsp;&nbsp;

"HH0750",&nbsp;&nbsp;&nbsp;

"HX0002",&nbsp;&nbsp;&nbsp;

"HX0003",&nbsp;&nbsp;&nbsp;

"HX0008",&nbsp;&nbsp;&nbsp;

"HX0010",&nbsp;&nbsp;&nbsp;

"HX0022",&nbsp;&nbsp;&nbsp;

"HX0106",&nbsp;&nbsp;&nbsp;

"HX0106",&nbsp;&nbsp;&nbsp;

"HX0107",&nbsp;&nbsp;&nbsp;

"HX0114",&nbsp;&nbsp;&nbsp;

"HX0115",&nbsp;&nbsp;&nbsp;

"SH0049",&nbsp;&nbsp;&nbsp;

"SH0504",&nbsp;&nbsp;&nbsp;

"SH0678",&nbsp;&nbsp;&nbsp;

"HH0113",&nbsp;&nbsp;&nbsp;

"HH0708",&nbsp;&nbsp;&nbsp;

"HH0746",&nbsp;&nbsp;&nbsp;

"HX0111",&nbsp;&nbsp;&nbsp;

"NU0017",&nbsp;&nbsp;&nbsp;

"NU0100",&nbsp;&nbsp;&nbsp;

"NU0200",&nbsp;&nbsp;&nbsp;

"NU0203",&nbsp;&nbsp;&nbsp;

"NU0206",

"NU0207",

"NU0213",

"NU0243",&nbsp;&nbsp;&nbsp;

"NU0244",

"NU0300",&nbsp;&nbsp;&nbsp;

"NU0301",

"NU0305",&nbsp;&nbsp;&nbsp;

"NU0327",&nbsp;&nbsp;&nbsp;

"NU0328",&nbsp;&nbsp;&nbsp;

"NU0362",&nbsp;&nbsp;&nbsp;

"NU0364",&nbsp;&nbsp;&nbsp;

"NU0400",&nbsp;&nbsp;&nbsp;

"NU0422",

"NU0423",

"NU0424",&nbsp;&nbsp;&nbsp;

"NU0425",&nbsp;&nbsp;&nbsp;

"NU0440",&nbsp;&nbsp;&nbsp;

"NU0461",&nbsp;&nbsp;&nbsp;

"NU0615",&nbsp;&nbsp;&nbsp;

"NU0618",&nbsp;&nbsp;&nbsp;

"NU0619",&nbsp;&nbsp;&nbsp;

"NU0620",&nbsp;&nbsp;&nbsp;

"NU0621",&nbsp;&nbsp;&nbsp;

"NU0622",&nbsp;&nbsp;&nbsp;

"NU0625",&nbsp;&nbsp;&nbsp;

"NU0800",&nbsp;&nbsp;&nbsp;

"NU0801",&nbsp;&nbsp;&nbsp;

"NU0923",&nbsp;&nbsp;&nbsp;

"NX0002",&nbsp;&nbsp;&nbsp;

"NX0006",&nbsp;&nbsp;&nbsp;

"NX0017",&nbsp;&nbsp;&nbsp;

"NX0018",&nbsp;&nbsp;&nbsp;

"NX0019",&nbsp;&nbsp;&nbsp;

"NX0023",&nbsp;&nbsp;&nbsp;

"NX0125",&nbsp;&nbsp;&nbsp;

"NX0465",

"NX0466",&nbsp;&nbsp;&nbsp;

"NX0609",&nbsp;&nbsp;&nbsp;

"NX0610",&nbsp;&nbsp;&nbsp;

"NU0610",&nbsp;&nbsp;&nbsp;

"NU0700",&nbsp;&nbsp;&nbsp;

"NU0710",&nbsp;&nbsp;&nbsp;

"NU0713",&nbsp;&nbsp;&nbsp;

"NU0922",

"NU0364",&nbsp;&nbsp;&nbsp;

"NX0609",&nbsp;&nbsp;&nbsp;

"NX0610"};

Mock Data Table.jmp

txnelson

Here is my rework of your code that seems to work

New Column( "myJOBCODE Status", Character, Nominal ) << Formula(
	If( Row() == 1,
		mylist = {"HH0029", "HH0030", "HH0241", "HH0242", "HH0317", "HH0635", "HH0635", "HH0636", "HH0636", "HH0639", "HH0742",
		"HH0750", "HX0002", "HX0003", "HX0008", "HX0010", "HX0022", "HX0106", "HX0106", "HX0107", "HX0114", "HX0115", "SH0049",
		"SH0504", "SH0678", "HH0113", "HH0708", "HH0746", "HX0111", "NU0017", "NU0100", "NU0200", "NU0203", "NU0206", "NU0207",
		"NU0213", "NU0243", "NU0244", "NU0300", "NU0301", "NU0305", "NU0327", "NU0328", "NU0362", "NU0364", "NU0400", "NU0422",
		"NU0423", "NU0424", "NU0425", "NU0440", "NU0461", "NU0615", "NU0618", "NU0619", "NU0620", "NU0621", "NU0622", "NU0625",
		"NU0800", "NU0801", "NU0923", "NX0002", "NX0006", "NX0017", "NX0018", "NX0019", "NX0023", "NX0125", "NX0465", "NX0466",
		"NX0609", "NX0610", "NU0610", "NU0700", "NU0710", "NU0713", "NU0922", "NU0364", "NX0609", "NX0610"}
	);
	isThere = Function( {value},
		{},
		If( Contains( mylist, value ),
			1,
			0
		)
	);
	a = :JOBCODE Timepoint1;
	b = :JOBCODE Timepoint2;
	If(
		isThere( a ) & b == "", "Termination/Separation",
		a == "" & isThere( b ), "New Employee",
		isThere( a ) & isThere( b ), "No Change"
	);
);