Search code examples
datastage

How can I determine the quarter for a given month in DataStage Transformer stage?


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.


Solution

  • 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...