Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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......);
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
please close your thread if your question is answered:
Qlik Community Tip: Marking Replies as Correct or Helpful
thanks
regards
Marco