Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Getting Max Date and Approver

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

1 Solution

Accepted Solutions

Re: Getting Max Date and Approver

See the attched one

2 Replies

Re: Getting Max Date and Approver

See the attched one

Not applicable

Re: Getting Max Date and Approver

Thank you very much!  Works great. 

Community Browser