Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a data set like this. I am trying to get counts here.
The condition is first get the minimum date for Type = Foreign, In this case , 8/1/2021, then get the counts for all domestic in that group comes after 8/1/21. The count here is 4.
GROUPID | KEY | TYPE | Shipment Date |
100 | 1 | DOMESTIC | 4/2/2021 |
100 | 2 | DOMESTIC | 4/1/2021 |
100 | 3 | FOREIGN | 2/2/2021 |
100 | 4 | DOMESTIC | 2/1/2021 |
100 | 5 | DOMESTIC | 1/1/2021 |
100 | 6 | FOREIGN | 8/1/2020 |
100 | 7 | DOMESTIC | 1/1/2020 |
Hi, not sure it could adapt in your project, from my understanding, I would suggest this:
Data:
Load GROUPID,
KEY,
TYPE,
Date(Date#([Shipment Date],'D/M/YYYY')) as [Shipment Date];
Load * INLINE
[
GROUPID, KEY, TYPE, Shipment Date
100 ,1, DOMESTIC, 4/2/2021
100 ,2, DOMESTIC, 4/1/2021
100 ,3, FOREIGN, 2/2/2021
100 ,4, DOMESTIC, 2/1/2021
100 ,5, DOMESTIC, 1/1/2021
100 ,6, FOREIGN, 8/1/2020
100 ,7, DOMESTIC, 1/1/2020
];
ForeignMap:
Mapping Load
GROUPID,
Date(Min([Shipment Date])) as Min_ShipDate
Resident Data where TYPE = 'FOREIGN'
Group by GROUPID;
Result:
Load *,
If([Shipment Date] > MinShipDate and TYPE = 'DOMESTIC', 1, 0) as CountFlag;
Load
GROUPID,
KEY,
TYPE,
[Shipment Date],
ApplyMap('ForeignMap',GROUPID, Null()) as MinShipDate
Resident Data;
Drop Table Data;
Drop Field MinShipDate;
Then in you visualization, just Sum the CountFlag field to get the count:
Hi, not sure it could adapt in your project, from my understanding, I would suggest this:
Data:
Load GROUPID,
KEY,
TYPE,
Date(Date#([Shipment Date],'D/M/YYYY')) as [Shipment Date];
Load * INLINE
[
GROUPID, KEY, TYPE, Shipment Date
100 ,1, DOMESTIC, 4/2/2021
100 ,2, DOMESTIC, 4/1/2021
100 ,3, FOREIGN, 2/2/2021
100 ,4, DOMESTIC, 2/1/2021
100 ,5, DOMESTIC, 1/1/2021
100 ,6, FOREIGN, 8/1/2020
100 ,7, DOMESTIC, 1/1/2020
];
ForeignMap:
Mapping Load
GROUPID,
Date(Min([Shipment Date])) as Min_ShipDate
Resident Data where TYPE = 'FOREIGN'
Group by GROUPID;
Result:
Load *,
If([Shipment Date] > MinShipDate and TYPE = 'DOMESTIC', 1, 0) as CountFlag;
Load
GROUPID,
KEY,
TYPE,
[Shipment Date],
ApplyMap('ForeignMap',GROUPID, Null()) as MinShipDate
Resident Data;
Drop Table Data;
Drop Field MinShipDate;
Then in you visualization, just Sum the CountFlag field to get the count:
Thanks for the reply Nguyen, I was using something similar, used sql view for temp tables.
Just thought of checking if we can use peek with range sum in this case.