Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Please help me solve the below scenario ASAP as I am stuck in it.
I want those ID who have both status(i.e. In-queue and un-processed) is falling against same ID.
For example, I have column Status(In-queue, un-processed,processed, completed) and ID. i want all ID whose status( In-queue and un-processed) .
ID Status
1 In-queue
1 un-processed
2 processed
2 completed
3 In-queue
4 completed
5 In-queue
5 un-processed
6 completed
7 In-queue
7 un-processed
so my result will be ID: 1,5,7
Pls reply urgently.
Thanks in Advance .
Try this may be:
Table:
LOAD * INLINE [
ID, Status
1, In-queue
1, un-processed
2, processed
2, completed
3, In-queue
4, completed
5, In-queue
5, un-processed
6, completed
7, In-queue
7, un-processed
];
Right Join (Table)
LOAD ID
Where Flag = 2;
LOAD ID,
Count(DISTINCT Status) as Flag
Resident Table
Where Match(Status, 'In-queue', 'un-processed')
Group By ID;
Hi,
try in where clause
Load * from tableName
where status='In-queue' and status='un-processed'
Regards,
Try this may be:
Table:
LOAD * INLINE [
ID, Status
1, In-queue
1, un-processed
2, processed
2, completed
3, In-queue
4, completed
5, In-queue
5, un-processed
6, completed
7, In-queue
7, un-processed
];
Right Join (Table)
LOAD ID
Where Flag = 2;
LOAD ID,
Count(DISTINCT Status) as Flag
Resident Table
Where Match(Status, 'In-queue', 'un-processed')
Group By ID;
Hello Sunny T,
Can you explain me, why you have use Flag = 2;
Hi,
another solution can be
Table:
LOAD * INLINE [
ID, Status
1, In-queue
1, un-processed
1, new
2, processed
2, completed
3, In-queue
4, completed
5, In-queue
5, un-processed
6, completed
7, In-queue
7, un-processed
];
Test:
Load ID as new, count(Status) as newCo
Resident Table
Group by ID;
Test1:
Load new as newID
Resident Test
where newCo=2;
Left join
Load ID as newID,
Status
Resident Table;
FinalTest:
Load newID as FinalID,Status as FinalStatus Resident Test1
where wildmatch(Status,'In-queue','un-processed');
Drop table Table,Test,Test1;
Regards,
I did a distinct count of status, but I have used a where statement which only brings in the two type of statuses. So in essence I am trying to pull those ID's where the distinct count of statuses = 2 and where those two status matches In-queue and un-processed.
Does it make sense?
Hi All,
Thanks Sunny and Max Dreamer for solution.
Warm Regards,
Ashvita.
Not a problem. We are glad we were able to help.
Best,
Sunny