Getting Max Date and Approver

Tue, 06 Aug 2013 23:06:32 GMT

I have a database that looks something like this:

Invoice Vendor Role From Role To Approval Date Comments

Number Name

10001 XYZ Co. Smith Johnson 1/2/2013 First Approval 1

10001 XYZ Co. Johnson Jones 1/5/2013 Second Approval 1

10001 XYZ Co. Jones Williams 1/10/2013 Final Approval1

22222 ABC Co. Cook Jones 1/1/2013 First Approval 2

22222 ABC Co. Jones Williams 1/4/2013 Second Approval 2

22222 ABC Co. Moore Sullivan 1/5/2013 Final Approval 2

22222 ABC Co. Wiliams Moore 1/4/2013 Third Approval 2

321321 Acme In. Runner Coyote 1/3/2013 First Approval 3

321321 Acme In. Coyote Wile 2/1/2013 Final Approval3

5555 Co. Inc. Jones Smith 1/24/2013 Only Approval

I am trying to get the Invoice Number, Vendor Name and the earliest date with the Role From person, i.e.:

Invoice Vendor Role From Approval Date Comments

Number Name

1001 XYZ Co. Smith 1/2/2013 First Approval 1

22222 ABC Co. Cook 1/1/2013 First Approval 2

321321 Acme Inc. Runner 1/3/2013 First Approval 3

5555 Co. Inc. Jones 1/24/2013 Only Approval

And also the last person to approve it:

Invoice Vendor Role To Approval Date Comments

Number Name

1001 XYZ Co. Williams 1/10/2013 Final Approval 1

22222 ABC Co. Sullivan 1/5/2013 Final Approval 2

321321 Acme Inc. Wile 2/1/2013 Final Approval 3

5555 Co Inc. Smith 1/24/2013 Only Approval

I tried using Group By, but it does not give the correct names or comments. It seems to be pretty random what shows up in the comments and Roles fields. The max and min dates seem to work fine.

This is what I have for the Initial Approver portion.

QVDLOADTEMP:

"Invoice Number",

"Vendor Name",

"Role From",

"Role To",

"Approval Date",

...other fields...

"Comments"

from File.qvd (qvd);

load *; load "Invoice Number", min([Approval Date]) as MinApproval,

LastValue(RoleFrom) AS LastRoleFrom, LastValue(Comments) AS MinComments

resident QVDLOADTEMP

group by "Invoice Number";

Any help is appreciated.

Don

Re: Getting Max Date and Approver

Kushal_Chawda — Wed, 07 Aug 2013 05:10:21 GMT

See the attched one

Re: Getting Max Date and Approver

Wed, 07 Aug 2013 17:50:36 GMT

Thank you very much! Works great.

topic Re: Getting Max Date and Approver in QlikView

Getting Max Date and Approver

Re: Getting Max Date and Approver

Re: Getting Max Date and Approver