Exclude all records of ID which doesnot fall under given condition

[#1]

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

[sunny_talwar]

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;

[sorrakis01]

Hi,

Another one;

Use the flag to show repeated or not repeated.

Regards,