Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

shannoypaul
Contributor

Inter Record Functions / Computation

Hi folks,

I would need help to implement the following:

Most Recent.PNG

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

1 Solution

Accepted Solutions
Highlighted

Re: Inter Record Functions / Computation

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

Capture.PNG

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;

View solution in original post

8 Replies
Highlighted

Re: Inter Record Functions / Computation

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;

Highlighted
shannoypaul
Contributor

Re: Inter Record Functions / Computation

Thanks  Sunny.

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

Highlighted

Re: Inter Record Functions / Computation

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;

Highlighted
shannoypaul
Contributor

Re: Inter Record Functions / Computation

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

Highlighted
shannoypaul
Contributor

Re: Inter Record Functions / Computation

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

Thanks,

Shannoy

Highlighted

Re: Inter Record Functions / Computation

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

Highlighted

Re: Inter Record Functions / Computation

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

Capture.PNG

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;

View solution in original post

Highlighted
shannoypaul
Contributor

Re: Inter Record Functions / Computation

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

In this case:

First Sorted Value - Not Distinct.PNG

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