topic Re: group by model name in QlikView

group by model name

Sat, 19 Jul 2014 09:18:44 GMT

Hi All ,

I have a column calle model type whichis as follows :

MODEL TYPE:

hp pavillion

hP brav

hp g series

dell geo

dell alpha

dell matrix

sony light

sony smart

I want to know how to write a query for this so that i can have only model like hp, sony and dell in pie chart

Can some one help me plz

Thanks In Advance

Re: group by model name

qlikoqlik — Sat, 19 Jul 2014 10:14:16 GMT

You can use subfield function in the script. This gives you the substring using the delimiter

I am thinking space is the delimiter and position of the delimiter is 1.

SUBFIELD([MODEL TYPE],' ',1) as mODEL

Regards

Padma

Re: Re: group by model name

Anonymous — Sat, 19 Jul 2014 10:33:12 GMT

Does the attached give you food for thought ?

Left hand pie chart was sorted in the script and right hand one in the GUI.

Re: group by model name

qlikoqlik — Sat, 19 Jul 2014 10:42:08 GMT

HI Bill

I lost you, where is the pie chart that you are referring to

Thanks

Padma

Re: group by model name

its_anandrjs — Sat, 19 Jul 2014 10:42:11 GMT

Add calculated dimension in the Pie chart as

If( Lower( Match( Subfield( [MODEL TYPE],' ',1),'hp','dell','Sony') , [MODEL TYPE] )

Note:- And select Suppress when value null option.

Re: Re: group by model name

Anonymous — Sat, 19 Jul 2014 10:46:06 GMT

Open the qvw I attached and you will see the pie charts

Re: group by model name

qlikoqlik — Sat, 19 Jul 2014 10:48:42 GMT

Oh! Thanks

I will check when I get work as I have only personalised edition at home

Regards

Padma

Re: Re: group by model name

Anonymous — Sat, 19 Jul 2014 10:57:06 GMT

In the script :

Temp :

LOAD * INLINE [

MODEL, TYPE, METRIC

hp, pavilion, 1

hP, brav, 2

hp, g series, 3

dell, geo, 4

dell, alpha, 5

dell, matrix, 6

sony, light, 7

sony, smart, 8

];

DataTable:

load

MODEL ,

lower(MODEL) as Model ,

TYPE ,

METRIC

resident Temp

;

drop table Temp

In the GUI :

Calculated dimension =lower(MODEL)