Split Column Based on Text

sagrim

Good afternoon.  I am attempting to split one column into three new columns and place a piece of the string from the original column into one of the three new columns based on the context of the text.  I understand that text to columns would parse out the string based on some delimiter, however, this doesn’t quite get me where I need to be.  I need a function that reads the text, checks for certain keywords (years, months or days) and then places a piece of the string (the numerical value that precedes “years”, “months” or “days”) in the appropriate column.  I see that the word() function can extrapolate the first word or value and this would be a start but then the value still needs to be placed in one of three potential columns based on the text. Does anyone have thoughts on how to code this?  Or ideas for a potential work-around?  Any insight is greatly appreciated. 

 

Example attached where column 1 shows the data in its current format and columns 2-4 are the ideal state.  Thank you!

 

Stephanie

Example dt.jmp

txnelson

Here is one way to handle this

Names Default To Here( 1 );
dt = New Table( "Example dt",
	Add Rows( 10 ),
	Set Header Height( 46 ),
	New Column( "Age",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"2-day old", "43-day old", "3-day old",
			"3-month old", "4-month old", "5-day old",
			"32-day old", "13-year old", "10-year old",
			"10-month old"}
		)
	)
);
dt << New Column( "AgeYears" );
dt << New Column( "AgeMonths" );
dt << New Column( "AgeDays" );

For( i = 1, i <= N Rows( dt ), i++,
	Match( Word( 2, :Age[i], "- " ),
		"day",
			:AgeDays[i] =
			Num( Word( 1, :age[i], "- " ) ),
		"month",
			:AgeMonths[i] =
			Num( Word( 1, :age[i], "- " ) ),
		"year",
			:AgeYears[i] =
			Num( Word( 1, :age[i], "- " ) )
	)
);

 Above was corrected as noted.