Qlik sense, how to categorize a field into percentage

[Anonymous]

How to categorize a field into percentage in Qlik sense load data editor?

The column is my_value:

try to use:

load

(my_value - min(my_value))/(max(my_value)-min(my_value)) as value_category

and then use:

if (value_category> 0.5, 'A',

if(value_category>0.3, 'B', 'C'

))

However, the first part does not work. Any suggestion is appreciated!

[petter]

You will have to first calculate the Min and Max values of my_value. Then you can use those in the calculation afterwards.

LOAD    

     Min(my_value) AS MinValue,

     Max(my_value) AS MaxValue

FROM

    [xyz.xlsx] (ooxml......);

vMinValue = Peek('MinValue');

vMaxValue = Peek('MaxValue');

LOAD

     (my_value - $(vMinValue)) / ( $(vMaxValue) - $(vMinValue)) AS value_category,

    .......

FROM

     [xyz.xlsx] (ooxml......);

[MarcoWedel · Accepted solution]

Hi,

another example might be:

table1:
LOAD Ceil(Rand()*50)+50 as myValue
AutoGenerate 20;
Join
LOAD Min(myValue) as myMinValue,
    Max(myValue) as myMaxValue
Resident table1;
Join
LOAD Distinct
    *,
    If(value_percentage>0.5,'A',If(value_percentage>0.3,'B','C')) as value_category;
LOAD myValue,
    Num((myValue-myMinValue)/(myMaxValue-myMinValue),'0.0%') as value_percentage
Resident table1;

hope this helps

regards

Marco

[MarcoWedel]

please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco