Distinct Count based on Multiple conditions(AND NOT)

[devips]

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

[BrunPierre]

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#;