ID's depending on condition

[#1]

Hi All,

Kindly suggest solution for my below mentioned issue.

ID  ,   Status

1   ,   Active

1   ,   In- Active

1   ,   Active

1   ,   Active

2   ,   In- Active

3   ,   Active

3   ,   Active

4   ,   Active

5   ,   Active

5  ,    In- Active

5  ,    Active

5  ,    In- Active

6  ,    Active

6  ,    In- Active

7  ,    Active

7  ,    Active

7  ,    In- Active

7  ,    In- Active

8  ,    In- Active

9  ,    Active

10 ,   In- Active

10 ,   Active

I want only those ID's who does not have In- Active Status .

My result will be :

ID

3

4

9

Hope u guys got my requirement and will help me asap.

Regards,

Minal Gedam

[avinashelite]

Try like this

Temp:

LOAD

ID  ,  

Status

from table;

Result:

LOAD

ID,

concat(Status,',') as New_Status

wildmatch(concat(Status,','),'*In- Active*') as Flag

Resident

Temp

group by

ID;

in front end try check for the flag 0

[avinashelite]

if you need in the back end try like this

Temp:

LOAD

ID  , 

Status

from table;

Result:

LOAD

ID,

concat(Status,',') as New_Status

wildmatch(concat(Status,','),'*In- Active*') as Flag

Resident

Temp

group by

ID;

Noconcatenate

LOAD

ID,

concat(Status,',') as New_Status

resident

Result

where Flag=0;

drop table Result;

[PrashantSangle]

Hi,

try like

Load Id ,Status

where not wildmatch(staus,'In-active')

Regards

[#5]

Hi max,

Ur solution will not give the result as I mentioned in my post .

Pls suggest something else

[#6]

Hi Avinash R,

I tried you solution but it is not working

[PrashantSangle]

Hi,

Complete code

Test:

LOAD * INLINE [

ID  ,   Status

1   ,   Active

1   ,   In- Active

1   ,   Active

1   ,   Active

2   ,   In- Active

3   ,   Active

3   ,   Active

4   ,   Active

5   ,   Active

5  ,    In- Active

5  ,    Active

5  ,    In- Active

6  ,    Active

6  ,    In- Active

7  ,    Active

7  ,    Active

7  ,    In- Active

7  ,    In- Active

8  ,    In- Active

9  ,    Active

10 ,   In- Active

10 ,   Active

];

Temp:

Load ID as InActiveID,

Status as NEwStat

Resident Test

where WildMatch(Status,'In- Active');

NoConcatenate

Final:

Load * Resident Test

where not Exists(InActiveID,ID);

Drop table Temp,Test;

Regards,

Prashant

[PrashantSangle]

Hi,

Above code giving me below output.

Your required output is

3

4

9

which you mention in your post. I don't get it why it is not working at your end.

Can you post your sample app.

Regards,

Prashant

[PrashantSangle]

Hi,

Please find attachment for your reference.

Regards,

Prashant

[veeranj]

Hi,

please check the below code .

temp:

LOAD * INLINE [

    id, status

    1, active

    1, inactive

    1, active

    2, active

    2, inactive

    3, active

    3, inactive

    4, active

    4, active

    5, active

];

ids_having_atleast_one_inactivestatus:

load  id as id1  Resident  temp where status='inactive';

output:

noconcatenate

load *,'required output' as output Resident temp where not  exists(id1,id) ;

drop tables temp,ids_having_atleast_one_inactivestatus;

here output will be 4 and 5 as per my input .

Thanks,

Anjee