topic Re: Finding Key Dates and Associated Values in Load Script (Back End) in QlikView

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

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

sunny_talwar — Fri, 24 Mar 2017 16:40:21 GMT

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

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

Anonymous — Fri, 24 Mar 2017 16:59:06 GMT

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

];

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?

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

sunny_talwar — Fri, 24 Mar 2017 17:41:35 GMT

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;

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

sunny_talwar — Fri, 24 Mar 2017 17:44:30 GMT

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

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

Anonymous — Fri, 24 Mar 2017 17:50:26 GMT

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.

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

sunny_talwar — Fri, 24 Mar 2017 17:52:08 GMT

Sounds like a plan

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

Anonymous — Fri, 24 Mar 2017 19:27:37 GMT

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?

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

sunny_talwar — Fri, 24 Mar 2017 19:29:15 GMT

Checking...

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

sunny_talwar — Fri, 24 Mar 2017 19:30:39 GMT

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;

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

Anonymous — Fri, 24 Mar 2017 19:32:07 GMT

Making the change and reloading.

What is the purpose of doing this division by 10000000000?

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

sunny_talwar — Fri, 24 Mar 2017 19:33:26 GMT

Giving lower weight in the sorting... here I want to sort first by GiftAmount and then by GiftDate...

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

Anonymous — Fri, 24 Mar 2017 19:35:34 GMT

OK, I think I understand.

Is this in case the Gift are all the same amount, so there is not a unique "Max" gift?

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

sunny_talwar — Fri, 24 Mar 2017 19:37:53 GMT

Absolutely!! Since there are multiple gifts which are max, in order to pull the max date, I need to add that to the sorting. So, Max Gift on Max Date

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

Anonymous — Fri, 24 Mar 2017 21:18:41 GMT

Everything is loaded in and it's working perfectly.

Thanks stalwar1!