Finding Key Dates and Associated Values in Load Script (Back End)

[Anonymous]

Hello world!

I am looking for a load solution (not front in calculation) if possible for finding the date of the highest value in a table.

I have a Donor table that has Fields like: Donor ID, Name, Initial Gift Date, Last Gift Date, Max Gift Date

and I have a Gift table with Field: Gift ID, Donor ID, Gift Amount, Gift Date, Gift Type

What I need to answer are these questions

For each Donor:

1) When was their first gift and for how much was the gift for?

2) When was their most recent gift and for how much?

3) What is the largest gift this donor has given and what is the date on that gift?

I think these will need to end up joined to the Donor table, though the information comes from the gift table.

I have seen a post by stalwar1‌ on a similar topic for a front end solution here: https://qliktech.hosted.jivesoftware.com/docs/DOC-9044

but I would like a backend solution where this is done in the load script to manage the data more easily.

Thanks in advance!

[sunny_talwar]

Would you be able to share some raw data and from it what is the output you are hoping to see?

[Anonymous]

I'll need to make up some data (everything is highly sensitive), but I can give you the form of it. Let me know if this isn't enough

The Donor Table has Donor ID, Name, and other fields, but I want to add the fields I've mentioned (Initial Gift Date, Initial Gift Amount, Last Gift Date, Last Gift Amount, Max Gift Date, Max Gift Amount).

Currently

Donor:

LOAD * INLINE [

    DonorID

    1, 

    2,

    3,

    4,        

];

Gift:

LOAD * INLINE [

    DonorID, GiftID, GiftDate, GiftAmount

    1, 1, 1/1/2017, 100 

    1, 2, 2/1/2017, 500

    1, 3, 3/1/2017, 200

    2, 1, 1/1/2017, 50 

    2, 2, 2/5/2017, 50

    3, 1, 3/1/2017, 200

    4, 1, 1/2/2017, 1000

    4, 2, 2/1/2017, 100    

];

-------------------------------------------------------------------------------------

Desired ( this could be in a separate table as well, like DonorGiftsCalcs):

Donor:

LOAD * INLINE [

    DonorID, IGiftDate, IGiftAMT, LGiftDate, LGiftAMT, MGiftDate, MGiftAMT

    1, 1/1/2017, 100, 3/1/2017, 200, 2/1/2017, 500 

    2, 1/1/2017, 50, 2/5/2017, 50, 2/1/2017, 50

    3, 3/1/2017, 200, 3/1/2017, 200, 3/1/2017, 200

    4, 1/2/2017, 1000, 2/1/2017, 100, 1/2/2017, 1000        

];

When a Donor has a a lot of gifts and there are multiple "max gifts" I would like to store the date of the most recent max gift.

Does this help?

[sunny_talwar]

Something like this

Gift:

LOAD * INLINE [

    DonorID, GiftID, GiftDate, GiftAmount

    1, 1, 1/1/2017, 100

    1, 2, 2/1/2017, 500

    1, 3, 3/1/2017, 200

    2, 1, 1/1/2017, 50

    2, 2, 2/5/2017, 50

    3, 1, 3/1/2017, 200

    4, 1, 1/2/2017, 1000

    4, 2, 2/1/2017, 100   

];

Donor:

LOAD DonorID,

  Date(Min(GiftDate)) as IGiftDate,

  FirstSortedValue(GiftAmount, GiftDate) as IGiftAMT,

  Date(Max(GiftDate)) as LGiftDate,

  FirstSortedValue(GiftAmount, -GiftDate) as LGiftAMT,

  FirstSortedValue(GiftDate, -(GiftAmount+(GiftDate/1E10))) as MGiftDate,

  FirstSortedValue(GiftAmount, -(GiftAmount+(GiftDate/1E10))) as MGiftAMT

Resident Gift

Group By DonorID;

[sunny_talwar]

Only place it doesn't match is MGiftDate for DonorID = 2. It might be a typo in the input or output I guess because the underlying data doesn't have 2/1/2017

[Anonymous]

Ah, yes. Typo on my part. That should be, 2/5/2017, as your solution shows. Nice catch.

I'm checking the solution now in my load, will update once finished. Thanks.

[sunny_talwar]

Sounds like a plan

[Anonymous]

I should've mentioned initially, I'm using Qlik Sense, if that makes a difference.

When I put in the script I get an error highlight at "1E10"

Thoughts?

[sunny_talwar]

Checking...

[sunny_talwar · Accepted solution]

It shows error, but it runs.... if you wish to avoid the error, you can try this

Gift:

LOAD * INLINE [

    DonorID, GiftID, GiftDate, GiftAmount

    1, 1, 1/1/2017, 100

    1, 2, 2/1/2017, 500

    1, 3, 3/1/2017, 200

    2, 1, 1/1/2017, 50

    2, 2, 2/5/2017, 50

    3, 1, 3/1/2017, 200

    4, 1, 1/2/2017, 1000

    4, 2, 2/1/2017, 100 

];

Donor:

LOAD DonorID,

  Date(Min(GiftDate)) as IGiftDate,

  FirstSortedValue(GiftAmount, GiftDate) as IGiftAMT,

  Date(Max(GiftDate)) as LGiftDate,

  FirstSortedValue(GiftAmount, -GiftDate) as LGiftAMT,

  FirstSortedValue(GiftDate, -(GiftAmount+(GiftDate/10000000000))) as MGiftDate,

  FirstSortedValue(GiftAmount, -(GiftAmount+(GiftDate/10000000000))) as MGiftAMT

Resident Gift

Group By DonorID;