topic Re: derived column based on row level values in QlikView

derived column based on row level values

saurabh5 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: derived column based on row level values

swuehl — Wed, 17 Aug 2016 21:08:33 GMT

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

Counters in the Load

Re: derived column based on row level values

sunny_talwar — Wed, 17 Aug 2016 21:43:47 GMT

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;