topic Re: Sum based on value and give new name in QlikView

Sum based on value and give new name

marcusbohman — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I read a file with categories and values as below. There are many categories and some of them have very low value so I want to group them into a new category called "Others".

Category	Value
1	100
2	200
3	300
4	5
5	10

I want to make a static selection of which categories to sum as others so the output is as below.

Category	Value
1	100
2	200
3	300
Others	15

Re: Sum based on value and give new name

Or — Sun, 02 Sep 2018 09:16:50 GMT

If it's static, just use

Load if(Category = 4 or Category = 5, 'Others',Category) as Category, Value.

You can then use sum(Value) in your expressions.

If you need to do this at the script level, you can - you'll just have to GROUP BY the new category.

Likewise, if it's dynamic, you can still use the same thing -

Load if(Value < 20,'Others',Category) as Category, Value.

Re: Sum based on value and give new name

marcusbohman — Sun, 02 Sep 2018 09:40:02 GMT

Thank you Or,

I would like to have the detailed level kept, for example in another sheet or so. Is it possible to do in the expression of a chart or table?

Should of course included that in my initial question. Sorry.

Re: Sum based on value and give new name

Or — Sun, 02 Sep 2018 10:10:10 GMT

Not a problem at all - just load the original category as well.

Load Category as OriginalCategory, if(Category = 4 or Category = 5, 'Others',Category) as Category, Value