How do I conditionally LOAD rows in a table based on the value of a single field in the row?

[ksasidhars]

hi, I've the table as below; need help

LOAD * Inline [
ID,Status
1,Open
1,Complete
2,Complete
3,Open
3,Complete];

I want to have the final table as

ID,Status
1,Open
2,Complete
3,Open

Basically single ID and if Status is Open for the ID in any Row then display status as 'open' else 'complete'

thank you

[sunny_talwar · Accepted solution]

You can try this

Table:
LOAD *,
Match(Status, 'Open', 'Complete') as Status_Num_Value;
LOAD * INLINE [
ID, Status
1, Open
1, Complete
2, Complete
3, Open
3, Complete
];

Right Join (Table)
LOAD ID,
Min(Status_Num_Value) as Status_Num_Value
Resident Table
Group By ID;

[ksasidhars]

Thank you Sunny. Appreciated the quick response 

I'll try to customize this for my data.