Creating a field value from multiples values in a dimension

[ishafate]

Hi all 

I have a dimension called 'Fruits' and measure 'quantity'

In that I have list of fruit names and i am counting the quantity

Red Grape. 10

Red apple, 15

pineapple 10

banana 15

.

.

.

What I want to do is to have a red fruits as a category and show the rest of the fruits as individual and then use measure 'quantity' to count them 

thats:

Red fruits 25

Pineapple 10

Banana  15

Like this

How would I do this in Qlik?

Thank you in advance

[Mark_Little]

HI @ishafate 

If the Above example a simple IF statement would work.

In Script

If(Left(Fruits,3) = 'Red',

'Red Fruits',

Fruits

)  as Fruits.

[SunilChauhan]

dimension 

if( wildMatch(upper(FieldName,)*RED*')>0,'RED','Individual') as NewDimesion   // in Script

use New DImension in  Chart or Table

and expression 

sum( FruitQuantity)

[Ruhulessin]

Hi @ishafate,

You could also map the category in your table with a mapping table.
This table you can enrich in the future if you want to add more categories.

MapFruitCategory:
MAPPING LOAD * INLINE [
Fruit, Category
Red grape, Red fruits
Red apple, Red fruits
];
Table:
LOAD
     *, ApplyMap('MapFruitCategory', Fruit, Fruit)     AS Category
;
LOAD * INLINE [
Fruit, Quantity
Red grape, 10
Red apple, 15
Pineapple, 10
Banana, 15
];

-Ruben

[Chintam]

Hi, Try this , you will get your desired Output

Temp:
load Fruit_1, sum(Qty) as Qty Group By Fruit_1;
load *, if(wildMatch(Fruit,'Red*'),'Red Fruits',Fruit) as Fruit_1;

load * Inline [
Fruit, Qty
Red Apple, 10
Red Grapes, 15
Banana, 20
Pineapple,15
];