topic Re: Inter Record Functions / Computation in QlikView

Inter Record Functions / Computation

shannoypaul — Mon, 15 Oct 2018 11:10:06 GMT

Hi folks,

I would need help to implement the following:

for Row 4, I can use Previous() however I would want a generic solution that works alike for all the cases.

TIA,

Shannoy

Message was edited by: Shannoy Panankoodan Edit: Attached the excel

Re: Inter Record Functions / Computation

sunny_talwar — Mon, 15 Oct 2018 11:17:31 GMT

If you always want to subtract the value associated with the smallest date, then why don't you join this value and then do subtraction... something like this

Table:

LOAD [Created Date],

[Key Field],

[Value]

FROM ...;

Join (Table)

LOAD FirstSortedValue(Value, [Created Date]) as MinValue

Resident Table;

FinalTable:

LOAD *,

Value - MinValue as Variance

Resident Table;

DROP Table Table;

Re: Inter Record Functions / Computation

shannoypaul — Tue, 16 Oct 2018 11:19:25 GMT

Thanks Sunny.

your solution works fine for one set of key. What workaround can be done for multiple keys as shown below

Re: Inter Record Functions / Computation

sunny_talwar — Tue, 16 Oct 2018 11:21:17 GMT

This

Table:

LOAD [Created Date],

[Key Field],

[Value]

FROM ...;

Join (Table)

LOAD

[Key Field],

FirstSortedValue(Value, [Created Date]) as MinValue

Resident Table

Group By [Key Field];

FinalTable:

LOAD *,

Value - MinValue as Variance

Resident Table;

DROP Table Table;

Re: Inter Record Functions / Computation

shannoypaul — Tue, 16 Oct 2018 11:24:11 GMT

Currently trying to append the Key and Created date and using that as the sort_weight for the first sorted value function.

Let me know if that is not the correct way forward.

TIA,

Shannoy

Re: Inter Record Functions / Computation

shannoypaul — Tue, 16 Oct 2018 11:26:02 GMT

This did not yield the desired outcome, still I will do another round of Test.

Thanks,

Shannoy

Re: Inter Record Functions / Computation

sunny_talwar — Tue, 16 Oct 2018 11:27:47 GMT

That won't work because weight needs to be a number and not text

Re: Inter Record Functions / Computation

sunny_talwar — Tue, 16 Oct 2018 11:38:44 GMT

Seem to be working okay for me based on the data provided

Table:

LOAD * INLINE [

Created Date, Key Field, Value

10/31/2018, Key 1, 100

11/2/2018, Key 1, 200

12/01/2018, Key 1, 800

12/22/2018, Key 1, 500

11/2/2018, Key 1, 250

10/30/2018, Key 2, 100

11/14/2018, Key 2, 200

12/31/2018, Key 2, 800

];

Join (Table)

LOAD [Key Field],

FirstSortedValue(Value, [Created Date]) as MinValue

Resident Table

Group By [Key Field];

FinalTable:

LOAD *,

Value - MinValue as Variance

Resident Table;

DROP Table Table;

Re: Inter Record Functions / Computation

shannoypaul — Thu, 13 Dec 2018 05:59:17 GMT

If the values are not Distinct then what can be a work around?

In this case:

Recommended Approach:

Add the field (flag) to the Key to yield distinct results.

However Cannot do the same due to the business logic I am trying to achieve would be jeopardized