derived column based on row level values

[saurabh5]

hello All,

i am trying to create a derived column based on row level field value change. For ex. if an ID has rating increased from 1 quarter to other then the  derived column should  say increased if the rating decreased in the next quarter then decreased if not the unchanged.

      ID    As of Date Quarter    Rating    derived field

1033759    2014Q2                     2            

1033759    2014Q3                     4             increased

1033759    2014Q4                     4             unchanged

1035495    2014Q2                     4            

1035495    2014Q3                     4             unchanged

1035495    2014Q4                     4             unchanged

1035495    2015Q1                     4             unchanged

1035495    2015Q2                     4             unchanged

1035495    2015Q3                     4             unchanged

1300910    2014Q2                     2            

1300910    2014Q3                     3             increased

any thoughts...

-Saurabh

[swuehl]

You can use Peek() or Previous() to check for changes in ID and Rating, similar to what is shown here:

Counters in the Load

[sunny_talwar · Accepted solution]

Something along these lines:

Table:

LOAD * INLINE [

    ID,    As of Date Quarter,    Rating

    1033759,    2014Q2,          2

    1033759,    2014Q3,          4

    1033759,    2014Q4,          4

    1035495,    2014Q2,          4

    1035495,    2014Q3,          4

    1035495,    2014Q4,          4

    1035495,    2015Q1,          4

    1035495,    2015Q2,          4

    1035495,    2015Q3,          4

    1300910,    2014Q2,          2

    1300910,    2014Q3,          3

];

FinalTable:

LOAD *,

  If(ID = Previous(ID),

  If(Rating > Previous(Rating), 'Increased',

  If(Rating < Previous(Rating), 'Decreased', 'Unchanged'))) as [Derived Field]

Resident Table

Order By ID, [As of Date Quarter];

DROP Table Table;