topic Re: Replace values with one value for unique ID in QlikView

Replace values with one value for unique ID

safik003 — Fri, 28 Feb 2020 09:38:29 GMT

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 — Fri, 28 Feb 2020 10:24:50 GMT

attached

kaushiknsolanki — Fri, 28 Feb 2020 10:26:36 GMT

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;