Display only those id's showing both status.

[#1]

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 .

[PrashantSangle]

Hi,

try in where clause

Load * from tableName

where status='In-queue' and status='un-processed'

Regards,

[sunny_talwar · Accepted solution]

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;

[#4]

Hello Sunny T,

Can you explain me, why you have use Flag = 2;

[PrashantSangle]

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,

[sunny_talwar]

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?

[#7]

Hi All,

Thanks Sunny and Max Dreamer for solution.

Warm Regards,

Ashvita.

[sunny_talwar]

Not a problem. We are glad we were able to help.

Best,

Sunny