topic Display max value and other related fields in QlikView

Display max value and other related fields

Sun, 10 Mar 2013 23:53:09 GMT

Hello everyone,

I need help in accomplishing the final product. See step by step information below:

Raw data:

Name	Dept	Dept % 1	Dept % 2	Role
Maria	LT	25%	80%	Core
Maria	BT	55%	0%	Embed
John	LT	75%	25%	Embed
Lisa	CT	0%	100%	Core
Lisa	LT	60%	20%	Embed

Step 1:

create column 5. See formula in column title for more information.

Name	Dept	Dept % 1	Dept % 2	=IF(Dept % 2 >=0.35, '>35%','<35%')	Role
1	2	3	4	5	6
Maria	LT	25%	80%	>35%	Core
Maria	BT	55%	0%	<35%	Embed
John	LT	75%	25%	<35%	Embed
Lisa	CT	0%	100%	>35%	Core
Lisa	LT	60%	20%	<35%	Embed

Step 2:

If duplicate name, select the row that has max value = (Dept %1 + Dep % 2).

Step 3: FINAL:

After eliminating duplicates,

Count name by role

Final Product:

Dept	=IF(Dept % 2 >=0.35, '>35%','<35%')	Count of Name by role = Core	Count of name by role = Embed
BT	>35%	1
JT	>35%		1
LT	<35%	1
	>35%	1

Re: Display max value and other related fields

nirav_bhimani — Mon, 11 Mar 2013 05:30:11 GMT

Hi,

You should do the group by Name, then find max out of that in the script only and then do the join with your main table with resident table which contain few columns in that.

Regards,

Nirav Bhimani

Re: Display max value and other related fields

sushil353 — Mon, 11 Mar 2013 05:33:59 GMT

Hi,

Please find the attached app.

HTH

Sushil

Re: Display max value and other related fields

Mon, 11 Mar 2013 14:29:05 GMT

Thank you Sushil for making time to help me with this. This worked.

Re: Display max value and other related fields

Mon, 11 Mar 2013 14:29:48 GMT

Thank you Sushil for making time to help me. This worked.