Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jjustingkm
Creator
Creator

Peek function ?? or Other methods for this question

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.

GROUPIDKEYTYPEShipment Date
1001DOMESTIC4/2/2021
1002DOMESTIC4/1/2021
1003FOREIGN2/2/2021
1004DOMESTIC2/1/2021
1005DOMESTIC1/1/2021
1006FOREIGN8/1/2020
1007DOMESTIC1/1/2020
1 Solution

Accepted Solutions
Quy_Nguyen
Specialist
Specialist

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:

Quy_Nguyen_0-1617786693138.png

 

View solution in original post

2 Replies
Quy_Nguyen
Specialist
Specialist

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:

Quy_Nguyen_0-1617786693138.png

 

jjustingkm
Creator
Creator
Author

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.