Replace values with one value for unique ID

[safik003]

Hi,

I have a table with ID and Value field. I want to replace all values with a single value for a condition.

For example if a ID contains "Apple" Replace all values with "Fruit" for a thatID, if it doesn't contain "Apple" replace with others.

Below is my table

Test:
Load * INLINE [
ID, Value
1, Apple
1, Orange
1, Banana
2, Apple
2, Mango
2, Date
3, Wallnut
4, Peas
5, Papaya
5, Apple
5, Orange
];

My output expectation is below.

Test2:

ID, Value
1, Fruit
2, Fruit
3, Others
4, Others
5, Fruit

Suggestion please. Sample file attached.

[Taoufiq_Zarra · Accepted solution]

attached

[kaushiknsolanki · Accepted solution]

Try this code.

Test:
Load * INLINE [
ID, Value
1, Apple
1, Orange
1, Banana
2, Apple
2, Mango
2, Date
3, Wallnut
4, Peas
5, Papaya
5, Apple
5, Orange
];

FindIDwithApple:
Load Distinct ID as AppleID
Resident Test where Value = 'Apple';

Final:
Load Distinct ID as ID1,If(Exists(AppleID,ID),'Fruit','Other') as Value1
Resident Test;