Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 !
A Straight Table with the following expression: =If(DeliveryType='Pickup', min(DeliveryDate), If(DeliveryType='DropOff', Max(DeliveryDate)))
dimensions BranchName and DeliveryType
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