Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
saurabh5
Creator II
Creator II

derived column based on row level values

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

1 Solution

Accepted Solutions
sunny_talwar

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;


Capture.PNG

View solution in original post

2 Replies
swuehl
MVP
MVP

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

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;


Capture.PNG

View solution in original post