Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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#;