Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
devips
Contributor
Contributor

Distinct Count based on Multiple conditions(AND NOT)

Hi Team, 

I have a requirement , where i need to find the count(Distinct P_BK)

Note : P_BK should be checked in all the rows and find the distinct count

Sample Dataset:

Date P_BK CCCode RecTypeName
2024-03-01 4194688 SC DB
2024-03-14 4195660 SC DB
2024-03-15 4196011 SC DB
2024-02-29 4196105 SC DB
2024-03-26 4196518 SC DB
2024-03-13 4196628 SC DB
2024-04-05 4197716 SC RP
2024-03-15 4198394 SC DB
2024-03-18 4198650 SC DB
2024-03-26 4199911 SC DB
2024-03-08 4201398 SC DB
2024-04-01 4204063 SC DB
2024-04-01 4204063 SC RP
2024-04-04 4248356 SC DB
2024-04-04 4248356 SC RP
2024-03-28 4314267 SC DB
2024-04-05 4318384 SC DB
2024-04-04 4324030 SC DB
2024-04-01 4333052 SC DB

Criteria 1 - (CCCode = SC and RecTypeName = DB ) and NOT (CCCode = SC and RecTypeName = RP )  then Expected O/P  = SC Only

Criteria 2 - (CCCode = SC and RecTypeName = RP ) and NOT (CCCode = SC and RecTypeName = DB)  then Expected O/P  = ORP Only

Criteria 3 - (CCCode = SC and RecTypeName = RP ) and  (CCCode = SC and RecTypeName = DB)  then Expected O/P  = SC and ORP

 

Expected_Output

Date P_BK CCCode RecTypeName Expected 0/P
2024-03-01 4194688 SC DB SC Only
2024-03-14 4195660 SC DB SC Only
2024-03-15 4196011 SC DB SC Only
2024-02-29 4196105 SC DB SC Only
2024-03-26 4196518 SC DB SC Only
2024-03-13 4196628 SC DB SC Only
2024-04-05 4197716 SC RP ORP Only
2024-03-15 4198394 SC DB SC Only
2024-03-18 4198650 SC DB SC Only
2024-03-26 4199911 SC DB SC Only
2024-03-08 4201398 SC DB SC Only
2024-04-01 4204063 SC DB SC and ORP
2024-04-01 4204063 SC RP SC and ORP
2024-04-04 4248356 SC DB SC and ORP
2024-04-04 4248356 SC RP SC and ORP
2024-03-28 4314267 SC DB ORP Only
2024-04-05 4318384 SC DB ORP Only
2024-04-04 4324030 SC DB ORP Only
2024-04-01 4333052 SC DB ORP Only
1 Reply
BrunPierre
Partner - Master II
Partner - Master II

Try

Temp:
LOAD * From <SourceTable>;

Join
Load P_BK,
Count(P_BK) -1 as Duplicates#
Resident Temp
Group By P_BK;

Final:
LOAD *,
If(Duplicates# = 0 and CCCode = 'SC' and RecTypeName = 'RP', 'ORP Only',
If(Duplicates# = 0 and CCCode = 'SC' and RecTypeName = 'DB', 'SC Only',
If(Duplicates# > 0, 'SC and ORP'))) as [Expected O/P]
Resident Temp;
DROP Table Temp;
DROP Field Duplicates#;