Find Row where somedate=Max(somedate) and deliveryType='foo'

[JustinDallas]

Hello Everyone,

So I am working on a chart where the only rows I want is showing either are the (earliest pickup date ) or (latest dropoff date).  In SQL, this is known as the greatest-n-per-query problem.  I'm not sure how to solve it in the Qlik world, but I feel like I need some combination of MAX,MIN and AGGR.  I've got a small image of what I am striving for, and I've marked the rows I don't want with an X.  My test data isn't complex and it looks like this.

Branches:
Load * inline
[
  BranchId ,BranchName
  1,NorthBranch
  2,CentralBranch
  3,WesternBranch
];


BranchDeliveries:
Load * inline
[
  BranchId ,DeliveryType,DeliveryDate,Goal
  1,'Pickup','1/1/2017'
  1,'DropOff','1/2/2017' 
  1,'DropOff','1/3/2017'
  1,'DropOff','1/4/2017'

  2,'Pickup','1/7/2017'
  2,'DropOff','1/8/2017'
  2,'DropOff','1/9/2017'

  3,'Pickup','1/7/2017'
  3,'DropOff','1/8/2017'
];

Any help is greatly appreciated.

[OmarBenSalem]

You can do it in sql then load what you want to load in Qlik.

Qlik performs this way:

load

A,

B

SELECT

A,

B,

C

FROM SOURCE;

The select part i reserved to the source; I mean, you can use the syntax you're using in SQL there.

In your case, you can build your condition in the Select part using what you're used to have then load *.

Exp:

Load*

select

//do your sql queries here to select the fields you wanna select

from source;

You don't have to use Qlik ssyntasx

Hope this helps !

[craigsutton]

A Straight Table with the following expression: =If(DeliveryType='Pickup', min(DeliveryDate), If(DeliveryType='DropOff', Max(DeliveryDate)))

dimensions BranchName and DeliveryType

[OmarBenSalem]

Now you want to do it with qlik in the script, here''s how you proceed:

Branches:

Load * inline

[

  BranchId ,BranchName

  1,NorthBranch

  2,CentralBranch

  3,WesternBranch

];

BranchDeliveries:

Load * inline

[

  BranchId ,DeliveryType,DeliveryDate

  1,Pickup,1/1/2017

  1,DropOff,1/2/2017

  1,DropOff,1/3/2017

  1,DropOff,1/4/2017

  2,Pickup,1/7/2017

  2,DropOff,1/8/2017

  2,DropOff,1/9/2017

  3,Pickup,1/7/2017

  3,DropOff,1/8/2017

];

NewTable:

load BranchId,DeliveryType, date(date#(DeliveryDate,'M/D/YYYY')) as Date

Resident BranchDeliveries ;

drop field DeliveryDate;

drop Table BranchDeliveries;

Final:

load BranchId,DeliveryType, max(Date) as DateDeliv Resident NewTable

  where DeliveryType='DropOff'

Group by BranchId,DeliveryType

;

load BranchId,DeliveryType, min(Date) as DateDeliv Resident NewTable

  where DeliveryType='Pickup'

Group by BranchId,DeliveryType

;

drop Table NewTable;

see the attached app