topic Re: Creating a field value from multiples values in a dimension in App Development

Creating a field value from multiples values in a dimension

ishafate — Fri, 16 Sep 2022 10:11:29 GMT

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

Re: Creating a field value from multiples values in a dimension

Mark_Little — Fri, 16 Sep 2022 10:35:08 GMT

HI @ishafate

If the Above example a simple IF statement would work.

In Script

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

'Red Fruits',

Fruits

) as Fruits.

Re: Creating a field value from multiples values in a dimension

SunilChauhan — Fri, 16 Sep 2022 11:49:25 GMT

dimension

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

use New DImension in Chart or Table

and expression

sum( FruitQuantity)

Re: Creating a field value from multiples values in a dimension

Ruhulessin — Fri, 16 Sep 2022 12:09:51 GMT

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

Re: Creating a field value from multiples values in a dimension

Chintam — Fri, 16 Sep 2022 12:34:43 GMT

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 ];