Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
attached
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;
attached
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;