topic Re: How to divide a groups based on the records in QlikView

How to divide a groups based on the records

rkpatelqlikview — Tue, 13 Feb 2018 10:45:58 GMT

Hi,

Can anyone please help to write the backend code for this scenario?

I have table which is having the stores and sales. I need to divide three groups.

Group1 is 0-100 sales, Group2 is 100-300 sales, above 300 sales - Group3.

How many vehicles having for each group.

I need to show group1 stores and groip2 and group3 stores in the list box and pie chart.

Thanks in advance.

Re: How to divide a groups based on the records

YoussefBelloum — Tue, 13 Feb 2018 10:56:22 GMT

Hi,

there is a lot of methods to do it, here is one example:

Sales_table:

Load

if(sales>=0 and sales<=100,'Group1', if(number>=100 and sales <=300,'Group2', if(sales>300,'Group3'))) as Group

FROM

Re: How to divide a groups based on the records

rkpatelqlikview — Tue, 13 Feb 2018 11:16:28 GMT

Thanks for your help. Can you please provide another scenario.

When am clicking on the transporter name. it should display that group name (1,2 or 3).

and there are Three list boxes Group1,Group2, Group3 It should the transporter names.

there are three Groups. The transporter is having the 0-100 fleets

that transporter will comes under the Group1, like this group2-100-300 fleets, above 300fleets- Group3

How many vehicles having for each group.

Re: How to divide a groups based on the records

rkpatelqlikview — Tue, 13 Feb 2018 11:21:43 GMT

This one i have given in my front end. Like this can i write in the backend.

=IF(Count(DISTINCT [Vehicle Number])<=100, 'Group1',

IF(Count(DISTINCT [Vehicle Number])>100 and Count(DISTINCT [Vehicle Number])<=300,'Group2',

IF(Count(DISTINCT [Vehicle Number])>300, 'Group3')))

Re: How to divide a groups based on the records

YoussefBelloum — Tue, 13 Feb 2018 13:00:02 GMT

Can you please attach sample data ?

Re: How to divide a groups based on the records

rkpatelqlikview — Tue, 13 Feb 2018 13:14:33 GMT

Thanks for your help

Sample Data.

TransName	Fleets
ABC	20
DEF	102
GHI	50
JKL	202
MNO	350
PQR	400
STU	450

ABC and GHI comes under Group1, DEF, JKL comes under Group2, MNO, PQR, STU comes under Group3.

Can i generate new fields for all these groups separately.

Re: How to divide a groups based on the records

YoussefBelloum — Tue, 13 Feb 2018 13:28:52 GMT

Please find attached

Re: How to divide a groups based on the records

rkpatelqlikview — Tue, 13 Feb 2018 13:36:06 GMT

Many Thanks Youssef,

I need to show count of group1 transnames etc..

Group1 - 2

Group2- 2

Group3 - 3

Re: How to divide a groups based on the records

YoussefBelloum — Tue, 13 Feb 2018 13:41:49 GMT

Please find attached,

Re: How to divide a groups based on the records

rkpatelqlikview — Tue, 13 Feb 2018 13:46:27 GMT

Thank you so much Youssef.

Re: How to divide a groups based on the records

YoussefBelloum — Tue, 13 Feb 2018 13:50:04 GMT

You're welcome !

good luck

Re: How to divide a groups based on the records

rkpatelqlikview — Tue, 13 Feb 2018 14:00:12 GMT

Thanks

May i know the reason its showing the field Group that containing only Group3.

its taking for all only Group3.

Load *,

if([Vehicle Number]<100,'Group1',if([Vehicle Number]>100 and [Vehicle Number]<300,'Group2','Group3')) as Group;

SND:

LOAD

[Carrier Name] AS [Transporter Name],

// Datums &'|'&[Carrier Code]&'|'&[Vehicle Number] as UnitKey,

[Vehicle Group],

[Vehicle Number],

VehicleTrips

FROM

[D:\Proje

table is Sheet1);

Re: How to divide a groups based on the records

YoussefBelloum — Tue, 13 Feb 2018 14:09:38 GMT

Can you attach your app ?

otherwise it can be caused by many things.. we should see.

be sure you don't have filters activated.

Re: How to divide a groups based on the records

rkpatelqlikview — Wed, 14 Feb 2018 03:29:58 GMT

Sure Youssef,

Thanks for your support. Please find the attached application. In that i should show the rating for the transporter based on the no.of vehicles and more kms travelled.

Thanks in advance.

Re: How to divide a groups based on the records

YoussefBelloum — Wed, 14 Feb 2018 08:56:36 GMT

Hi,

here I think you should make a group by.

you are making comparison on the field Vehicle number which is not a number, it is a char..

what is the red part ?

Re: How to divide a groups based on the records

rkpatelqlikview — Wed, 14 Feb 2018 11:28:15 GMT

Hi,

No need to consider that Red.

Re: How to divide a groups based on the records

rkpatelqlikview — Wed, 14 Feb 2018 11:29:51 GMT

Thanks Youssef,

Then how to write this one in the script level.

if([Vehicle Number]<100,'Group1',if([Vehicle Number]>100 and [Vehicle Number]<300,'Group2','Group3')) as Group;

Re: How to divide a groups based on the records

YoussefBelloum — Wed, 14 Feb 2018 12:06:58 GMT

The expression you mentiondd above won't work. You need an aggregation function like COUNT. And before you need to clean you field to remove the red part.

Re: How to divide a groups based on the records

rkpatelqlikview — Wed, 14 Feb 2018 12:13:26 GMT

Sure I will remove that.

Re: How to divide a groups based on the records

YoussefBelloum — Wed, 14 Feb 2018 13:10:54 GMT

try to remove it on the script and make Group calculation on the expressions

remove the red part on the script with this:

SubField([Vehicle Number],'(',-1) as [Vehicle Number]