I want to produce a label for a data record, listing the Year and Quarter, e.g., 2014-Q2.
In SQL I can accomplish this task with something like the following:
SELECT VARCHAR(YEAR_) CONCAT '-Q' CONCAT(CAST(CEILING(CAST(MONTH_) AS
DECIMAL(4, 2)) / 3) AS CHAR(1))) AS QTR_ FROM TABLE
I am attempting to gather the same result in a transformer stage in DataStage (v 8.5), I thought the following crude derivation would work:
If
(Link.MONTH_ =< 3)
Then (Link.YEAR_ : '-Q1')
Else
(If
(Link.MONTH_ > 3 and Link.MONTH_ =< 6)
Then (Link.YEAR_ : '-Q2')
Else
(If
(Link.MONTH_ > 6 and Link.MONTH_ =< 9)
Then (Link.YEAR_ : '-Q3')
Else
('Y' : DSLink2.YEAR : '-Q4')
)
)
Perhaps a transformer stage is not the best option, all suggestions welcome because right now the derivation kicks an error.
The following derivation performs sufficiently:
If (DataSet.MONTH < 4) Then DataSet.YEAR:"-Q1" Else If (DataSet.MONTH > 3 AND DataSet.MONTH < 7) Then DataSet.YEAR:"-Q2" Else If (DataSet.MONTH > 6 AND DataSet.MONTH < 10) Then DataSet.YEAR:"-Q3" Else DataSet.YEAR:"-Q4"
To produce:
2014-Q1
2014-Q2
etc...