Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Champion
Champion

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

Anonymous
Not applicable
Author

For grouping you can use the class function.

Class Function Example

YoussefBelloum
Champion
Champion

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

YoussefBelloum
Champion
Champion

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

petter
Partner - Champion III
Partner - Champion III

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;