topic Re: How to show rows only have top values? in QlikView

How to show rows only have top values?

Tue, 31 Mar 2015 16:22:17 GMT

Hi,

I have data as below. How to get the table in qvw only to show rows which is Max(No)

for eg:

considering below sample data I need to show output only the row

S908779 US 11/06/2013 20 19038 56 64563

ID	Region	DateManufacture	No	Kety	Moniyt	Notax
S908766	US	11/03/2013	20		0
S908767	US	11/03/2013	10	461	461
S908768	US	11/03/2013	8		461
S908769	US	01/04/2013	1	461	461
S908770	US	11/04/2013	10	461	461
S908771	US	11/04/2013	8	510	461
S908772	US	11/04/2013	20	345	355	23423
S908773	US	01/05/2013	1	461	461
S908774	US	11/05/2013	8	510	461
S908775	US	11/05/2013	10	461	461
S908776	US	11/05/2013	20	345	34	5534
S908777	US	01/06/2013	1	461	461
S908778	US	11/06/2013	10	461	461
S908779	US	11/06/2013	20	19038	56	64563
S908780	US	11/06/2013	8	510	461
S908781	US	01/07/2013	1	461	461
S908782	US	11/07/2013	10	461	461
S908783	US	11/07/2013	13	510	461
S908784	US	11/07/2013	8		461
S908785	US	11/07/2013	20	24	56	5465
S908786	US	01/08/2013	1	461	461
S908787	US	11/08/2013	10	461	461

Can anyone suggest me please.

Thanks.

Re: How to show rows only have top values?

sunny_talwar — Tue, 31 Mar 2015 16:26:08 GMT

There are other rows that have no = 20 why would you exclude them from your output for the constraint you have. Are you looking for Max(Kety) instead?? then the output would make sense.

Best,

Sunny

Re: How to show rows only have top values?

Tue, 31 Mar 2015 16:28:14 GMT

yes you are right. I need the Max(No) and the corresponding max values for this Max.

Is it possible?

please help me.

Re: How to show rows only have top values?

MK_QSL — Tue, 31 Mar 2015 16:28:54 GMT

What is Max(No)?

Re: How to show rows only have top values?

Tue, 31 Mar 2015 16:31:02 GMT

sorry. that I mean is Maximum value of the column No

Re: How to show rows only have top values?

MK_QSL — Tue, 31 Mar 2015 16:33:29 GMT

Can you provide few lines of sample data please?

Re: How to show rows only have top values?

Tue, 31 Mar 2015 16:39:03 GMT

Please see attached and help me.

Re: How to show rows only have top values?

Anonymous — Tue, 31 Mar 2015 19:39:39 GMT

Would using a Straight Table with the Dimensions you want and an Expression like this help ?

only ( { <No={'$(=max(No))'}>} No )

Re: How to show rows only have top values?

Wed, 01 Apr 2015 07:41:27 GMT

Thanks. This is showing all values for maximum 20. But I need to display only one row. so, for

ID = S908779 I need straight table to show only

S908779 US 11/06/2013 20 19038 56 64563

is it possible. Please help. I have tried many ways but nothing seems working.

Re: How to show rows only have top values?

settu_periasamy — Wed, 01 Apr 2015 07:51:12 GMT

Put like this

only ( { <No={'$(=max(No))'},Kety={'$(=max(Kety))'}>} No )

Re: How to show rows only have top values?

Wed, 01 Apr 2015 07:58:21 GMT

Thanks. Try this. when I used this table is not populating. all dimensions and expression disabled.

Any help please.

Re: How to show rows only have top values?

settu_periasamy — Wed, 01 Apr 2015 08:15:37 GMT

Hi,

I used the bill markham attachement, and update the expression like

only ( { <No={'$(=max(No))'},Kety={'$(=max(Kety))'}>} No )

Re: How to show rows only have top values?

MK_QSL — Wed, 01 Apr 2015 08:26:11 GMT

Amelia, Can you provide excel file with output for any 3-4 ID?

Re: How to show rows only have top values?

Wed, 01 Apr 2015 09:02:28 GMT

Thanks. Please see attached.

This have 24 IDs

Re: How to show rows only have top values?

MK_QSL — Wed, 01 Apr 2015 09:09:56 GMT

have you provided output also?

Re: How to show rows only have top values?

Wed, 01 Apr 2015 09:20:39 GMT

see for ID= Rt0976 I need output ss below.

ID	Region	DateManufacture	No	Kety	Moniyt	Notax
Rt0976	US	02/10/2014	13	677	613

Re: How to show rows only have top values?

buzzy996 — Wed, 01 Apr 2015 09:38:24 GMT

sorry....confused, but in ur above post u mentioned ur looking for ID=ID = S908779 but in latest post ur looking for ID=Rt0976!

pls clarify?

Re: How to show rows only have top values?

Wed, 01 Apr 2015 10:09:24 GMT

I have checked in my file ID = S908779 is presented only once. if I use S908779 anyhow it will show the maximum value because of presence only one time.

That is the reason I changed the ID to Rt0976

to make clear what I need.

Hope this makes sense.

Please any help. is it possible to get the output.

Re: How to show rows only have top values?

MK_QSL — Wed, 01 Apr 2015 10:20:30 GMT

Can you explain the Login again please..

Let me tell you what I understood...

1) ID Rt0976

2) Number NO should be Maximum so 13 for this ID

3) Now you have 39 Records left...which are fulfilling above criteria.

4) What is the Logic to get DateManufacture = 02/10/2014? as I am getting Date 02/09/2014...

Please clarify... Thanks

Re: How to show rows only have top values?

Wed, 01 Apr 2015 10:44:29 GMT

The one which I used to add output is as below

ID = Rt0976

maximum No = 13

Maximum Kety = 677

There are two dates 02/09/2014 and 02/10/2014 the Maximum I need is DateManufacture = 02/10/2014.

I know it is tricky. Is there any solution to show all max values.

Please help.