How to make group bucket

[rkpatelqlikview]

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

[petter]

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/ConditionalFun...

[ninnartx]

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

Interval Match Feature/Function

[rkpatelqlikview]

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

[rkpatelqlikview]

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.

!

[petter]

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

[rkpatelqlikview]

Hi

PFA excel

[petter]

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:

[sunny_talwar · Accepted solution]

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];

[rkpatelqlikview]

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