Inter Record Functions / Computation

[shannoypaul]

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

[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]

Thanks  Sunny.

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

[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]

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]

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 · Accepted solution]

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;

[shannoypaul]

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