topic Re: How to convert character date column running APR-14 to JAN-22 into Quarterly (Q1,Q2,Q3,Q4) labels? in Discussions

How to convert character date column running APR-14 to JAN-22 into Quarterly (Q1,Q2,Q3,Q4) labels?

DELANDJ1985 — Fri, 09 Jun 2023 18:09:02 GMT

In the past I have substracted Month only string (JAN) from Jan-14 and then recoded individual months to new label (JAN) = (Q1). However this tack is very manual, is there a formula i can embed to automate this in the future?

Re: How to convert character date column running APR-14 to JAN-22 into Quarterly (Q1,Q2,Q3,Q4) labels?

jthi — Tue, 18 Jan 2022 16:14:28 GMT

If you have JMP16 you could first convert the character column to date column with format pattern and then use Quarter function

Names Default To Here(1);

dt = Current Data Table();
Column(dt, 1) << Set Name("MonthYear");
Column(dt, 1) << Data Type(Numeric, 
	Format("Format Pattern", "<Mmm><-><YY>", 35),
	Input Format("Format Pattern", "<Mmm><-><YY>")
);

dt << New Column("Q", Numeric, Ordinal, Formula(Quarter(:MonthYear)));

If you don't have JMP16 you could do "conversion list" of months and then use that instead to get Quarters

Edit:

Formula for JMPs before 16 and no conversion to first column using list of months, contains(), word() and ceiling()

dt << New Column("Q",
	Numeric,
	Ordinal,
	Formula(
		Local({month_list = (month_list = {"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"})},
			Ceiling(Contains(month_list, Word(1, :MonthYear, "-")) / 3)
		)
	)
);

Re: How to convert character date column running APR-14 to JAN-22 into Quarterly (Q1,Q2,Q3,Q4) labels?

DELANDJ1985 — Tue, 18 Jan 2022 16:18:22 GMT

Appreciate the quick response - I'm on JMP 14 so do not have format pattern. I have added a sample file, could you perhaps show me how to use conversion list?

Thanks!

Re: How to convert character date column running APR-14 to JAN-22 into Quarterly (Q1,Q2,Q3,Q4) labels?

DELANDJ1985 — Tue, 18 Jan 2022 16:30:53 GMT

Attempted to apply but perhaps i have input formula with basic error?

Re: How to convert character date column running APR-14 to JAN-22 into Quarterly (Q1,Q2,Q3,Q4) labels?

jthi — Tue, 18 Jan 2022 16:37:47 GMT

Create new column and then add this as formula:

Local({month_list = (month_list = {"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"})},
			Ceiling(Contains(month_list, Word(1, :MonthYear, "-")) / 3)
		)

Make sure you have correct column name in :MonthYear (in image below it is A)

Re: How to convert character date column running APR-14 to JAN-22 into Quarterly (Q1,Q2,Q3,Q4) labels?

DELANDJ1985 — Wed, 19 Jan 2022 12:30:21 GMT

Thanks Jarmo - the local variable feature is new to me but looks like it could be a game changer if i get the hang of it!