topic Re: How to make group bucket in QlikView

How to make group bucket

rkpatelqlikview — Mon, 02 Apr 2018 08:51:28 GMT

Dear Experts,

Please explain some one on the below scenario.

I do have Stores and Employess list. and each store there are different number of employees.

I need to make them in to groups like Group1 -> Store is having 1to5 employees, Group2-> Store is having 6 to 10 employees,

Group3 -> 10-15 employees, Group4 -> 15-20 employess, Groups -> more than 20 employees.

How to separate these group in the backend?

Thanks for your support

Re: How to make group bucket

petter — Mon, 02 Apr 2018 09:23:29 GMT

The most straightforward is to use the Class() function:

Class( #Employees , 5 )

You can use it in the load script (back-end) but also in the UI directly in the tables/charts.

Here is the online documentation:

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

Re: How to make group bucket

ninnartx — Mon, 02 Apr 2018 09:52:08 GMT

Maybe IntervalMatch() in the back end if the range is fixed?

Interval Match Feature/Function

Re: How to make group bucket

rkpatelqlikview — Mon, 02 Apr 2018 09:56:06 GMT

Hi Petter,

Thanks for your response. Here i attached one sample QVW.

There is CarrierName having no of vechiles. some of Carriername having 5 vehicles and some 20 etc..

I need to categorize that CarrierName in to groups based on their count of Vehicles.

CarrierName having vehicles in between 1 to 5 then the group is Group1, 5 to 10 Group2, 10 to 15 Group3, 15 to 20 Group4,20 above Group5.

EX: CarrierName "ASHOKA TPT-ACL BHATAPARA' having 4Vehicles it should come under Group1.

I need to put a filter in the front end. Group. If select Group1 it should display the CarrierNames under the Group1.

thanks

Re: How to make group bucket

rkpatelqlikview — Mon, 02 Apr 2018 09:58:27 GMT

Yes it should be fixed in the back end. Please find the below

There is CarrierName having no of vechiles. some of Carriername having 5 vehicles and some 20 etc..

I need to categorize that CarrierName in to groups based on their count of Vehicles.

CarrierName having vehicles in between 1 to 5 then the group is Group1, 5 to 10 Group2, 10 to 15 Group3, 15 to 20 Group4,20 above Group5.

EX: CarrierName "ASHOKA TPT-ACL BHATAPARA' having 4Vehicles it should come under Group1.

I need to put a filter in the front end. Group. If select Group1 it should display the CarrierNames under the Group1.

Re: How to make group bucket

petter — Mon, 02 Apr 2018 10:17:51 GMT

It's no data in the QlikView app. It would be much better to get the Excel data anyway.

Re: How to make group bucket

rkpatelqlikview — Mon, 02 Apr 2018 10:40:06 GMT

PFA excel

Re: How to make group bucket

petter — Mon, 02 Apr 2018 11:42:42 GMT

Your load script could look like this:

LOAD [Carrier Code], 
     [Carrier Name], 
     Count(DISTINCT [Vehicle Number]) AS #Vehicles,
     Class(  Count(DISTINCT [Vehicle Number]) , 5 ) AS Group
FROM

(ooxml, embedded labels, table is Sheet1)
GROUP BY
  [Carrier Code],[Carrier Name]
;

Then you can create a Straight Table like this:

Re: How to make group bucket

sunny_talwar — Mon, 02 Apr 2018 11:46:01 GMT

May be this

Sample:

LOAD [Carrier Code],

[Carrier Name],

[Vehicle Number]

FROM

samplesource.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join (Sample)

LOAD [Carrier Code],

If(Count(DISTINCT [Vehicle Number]) <= 5, 'Group 1',

If(Count(DISTINCT [Vehicle Number]) <= 10, 'Group 2',

If(Count(DISTINCT [Vehicle Number]) <= 15, 'Group 3', 'Group 4'))) as Group

Resident Sample

Group By [Carrier Code];

Re: How to make group bucket

rkpatelqlikview — Mon, 02 Apr 2018 11:58:08 GMT

Thanks Petter for your solution. I need to put a filters

Re: How to make group bucket

rkpatelqlikview — Mon, 02 Apr 2018 11:58:49 GMT

Thanks Sunny. Yes Helpful to me.