topic Re: Group the numbers in App Development

Group the numbers

Anonymous — Fri, 16 Feb 2018 14:41:37 GMT

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.

I need to group these numbers in first column like this

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

Re: Group the numbers

YoussefBelloum — Fri, 16 Feb 2018 15:00:13 GMT

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

Re: Group the numbers

Anonymous — Fri, 16 Feb 2018 15:07:21 GMT

For grouping you can use the class function.

Class Function Example

Re: Group the numbers

YoussefBelloum — Fri, 16 Feb 2018 15:20:13 GMT

Hi,

you can't Group data using Class() function

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

It allows you to assign classes to your grouped data

Re: Group the numbers

Anonymous — Fri, 16 Feb 2018 15:28:31 GMT

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

Re: Group the numbers

Anonymous — Fri, 16 Feb 2018 15:32:36 GMT

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

Re: Group the numbers

YoussefBelloum — Fri, 16 Feb 2018 15:59:53 GMT

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

Re: Group the numbers

petter — Fri, 16 Feb 2018 17:23:21 GMT

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;