Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Kindly help me to solve my below scenario :-
I have 2 columns ID and Status .
ID Status
1 Process
1 Un-Process
1 In-Transit
1 Completed
2 Un-Process
2 Completed
2 In-Transit
2 In-queue
3 In-transit
3 Completed
3 In-queue
3 Dispatch
4 Process
4 Un-Process
4 In-Transit
4 Completed
5 Process
5 Completed
5 In-Transit
5 Dispatch
6 Process
6 Un -process
I want only those ID whose Staus is not equal to Process and Un-Process . (both status should be checked )
For eg : For ID : 1 , Process,Un-Process ,In-Transit ,Completed status is shown .
The moment I check that the status for ID = 1 is Process and Unprocess both then it should exclude all records of ID = 1.
Similar for ID =4 and ID =6
My result for above scenario will be
ID : 2 , 3, 5
Try this:
Table:
LOAD * INLINE [
ID, Status
1, Process
1, Un-Process
1, In-Transit
1, Completed
2, Un-Process
2, Completed
2, In-Transit
2, In-queue
3, In-transit
3, Completed
3, In-queue
3, Dispatch
4, Process
4, Un-Process
4, In-Transit
4, Completed
5, Process
5, Completed
5, In-Transit
5, Dispatch
6, Process
6, Un-Process
];
Left Join (Table)
LOAD ID,
If(Count(DISTINCT If(Match(Status, 'Process', 'Un-Process'), Status)) = 2, 0, 1) as Flag
Resident Table
Group By ID;
FinalTable:
LOAD ID,
Status
Resident Table
Where Flag = 1;
DROP Table Table;
Hi,
Another one;
Use the flag to show repeated or not repeated.
Regards,