Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

sachinnataraj
New Contributor II

Group the numbers

Hello,

How do i group the numbers in Qlik sense to create a table & a graph.

I have different numbers but i want to group these numbers in one order.

weight.PNG

I need to group these numbers in first column like this

result.PNG

What formula should i use and how can i get it in a table like above and also in a bar graph or line graph?

Thank you in advance

Sachin

7 Replies
YoussefBelloum
Esteemed Contributor

Re: Group the numbers

Hi,

you have two methods to Group your data:

1. on the script with a Group by

2. on the front-end with aggregation functions like Sum, Count, Aggr......

it depends on what you need

jsf_fasoli
Contributor II

Re: Group the numbers

For grouping you can use the class function.

Class Function Example

YoussefBelloum
Esteemed Contributor

Re: Group the numbers

Hi,

you can't Group data using Class() function

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ConditionalFun...

It allows you to assign classes to your grouped data

sachinnataraj
New Contributor II

Re: Group the numbers

Hello Youssef,

What if i use the front-end

can you give an example

columnA      ColumnB

45                    1

30                    2    

100                   5

75                    1

12                    13    

5                         1

25                    18

i need something like to be grouped in Qlik sense like this

columnA          columnB

0-25                    32

25-50                    2

50-75                    1

75-100                   5

How can i get this

jsf_fasoli
Contributor II

Re: Group the numbers

Yes, I wrote it wrong. I meant he could use that function to assing a name a label to the bucket.

YoussefBelloum
Esteemed Contributor

Re: Group the numbers

you can use this as a dimension:

=if(columnA>=0 and columnA<=25,'0-25',

if(columnA<=50 and columnA>=25,'25-50',

if(columnA<=75 and columnA>=50,'50-75',

if(columnA<=100 and columnA>=75,'75-100'))))


you can use Class() but you will be limited on the result

MVP
MVP

Re: Group the numbers

Here is a way to do it if you need non-uniform bins/bucket sizes. The IntervalMatch() function can be used along with a interval table that specify the different binsizes:

CARGO:

LOAD * INLINE [

ID, Weight,Neg,Op,Pos

1, 30,1

2, 30,,,1

20, 99,1

3, 100,1

4, 50,1,,1

5, 490,,1

6, 300,1

7, 299,,1

8, 101,1

9, 200,,1,

];


GROUPS:

LOAD * INLINE [

From,To,WeightGroup

0,100,100 KG

101,200,200 KG

201,300,300 KG

301,499,500 KG

];


INNER JOIN

  IntervalMatch( Weight )

  LOAD

    From, To

  RESIDENT

    GROUPS;


LEFT JOIN (GROUPS) LOAD * RESIDENT CARGO;

DROP TABLE CARGO;

RENAME TABLE GROUPS TO CARGO;


// DELETE THE NEXT LINES IF YOU NEED THE CARGO TABLE AND NOT JUST THE SUMMARY

GROUPED:

LOAD

WeightGroup,

    Sum(Neg) AS Negative,

    Sum(Neg)/Count(WeightGroup) AS NegRatio,

    Sum(Op) AS Open,

    Sum(Op)/Count(WeightGroup) AS OpenRatio,

    Sum(Pos) AS Positive,

    Sum(Pos)/Count(WeightGroup) AS PosRatio,

    Count(WeightGroup) AS Total

RESIDENT

CARGO

GROUP BY

WeightGroup;

DROP TABLE CARGO;