Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shannoypaul
Creator
Creator

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
sunny_talwar

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
sunny_talwar

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;

shannoypaul
Creator
Creator
Author

Thanks  Sunny.

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

sunny_talwar

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;

shannoypaul
Creator
Creator
Author

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

shannoypaul
Creator
Creator
Author

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

Thanks,

Shannoy

sunny_talwar

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

sunny_talwar

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;

shannoypaul
Creator
Creator
Author

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