topic Re: How to show only max values? in QlikView

How to show only max values?

OrlandoPM — Sun, 21 Mar 2021 04:37:17 GMT

Hi,

I have the following information grouped by the following dimensions

Table A:

Deno	Concentr	Forma	Farmac	Presentation	CodigoProd	Quantity
ACIDO	250 mg	TABLETA	CAPSULA	CAJA CAPS	78004	16,830	Max Value
ACIDO	250 mg	TABLETA	TABLETA REC	CAJA TAB	4107	190
ACIDO	250 mg	TABLETA	TABLETA REC	CAJA TAB	8172	1,722
					Total	18,742
TOXIC	500 gm	UNIDAD	PAST	TAB	153	85	Max Value
TOXIC	500 gm	UNIDAD	PAST	CAPS	600	50
					Total	135

Result:

The result should show the dimensions that correspond to the Max Value in Table A and the quantity is the total value.

Deno	Concentr	Forma	Farmac	Presentation	CodigoProd	Quantity
ACIDO	250 mg	TABLETA	CAPSULA	CAJA CAPS	78004	18,742
TOXIC	500 gm	UNIDAD	PAST	TAB	153	135

Please can someone suggest me how to do this?

Thanks

Re: How to show only max values?

Frank_Hartmann — Sun, 21 Mar 2021 12:12:11 GMT

Maybe like this:

tmp: Load * Inline [ Deno, Concentr, Forma, Farmac, Presentation, CodigoProd, Quantity, ACIDO, 250 mg, TABLETA, CAPSULA, CAJA CAPS, 78004, 16.830 ACIDO, 250 mg, TABLETA, TABLETA REC, CAJA TAB, 4107, 190 ACIDO, 250 mg, TABLETA, TABLETA REC, CAJA TAB, 8172, 1.722 TOXIC, 500 gm, UNIDAD, PAST, TAB, 153, 85 TOXIC, 500 gm, UNIDAD, PAST, CAPS, 600, 50 ]; left join Load Deno,sum(Quantity) as Sum, max(Quantity) as max Resident tmp Group by Deno; NoConcatenate Final: Load * Resident tmp Where Quantity =max; DROP table tmp; DROP fields max,Quantity;

Re: How to show only max values?

OrlandoPM — Sun, 21 Mar 2021 19:04:42 GMT

Thank you very much @Frank_Hartmann , it works perfectly, but I need both tables as a result, there is a possibility to get the same result using expressions.

Re: How to show only max values?

MayilVahanan — Mon, 22 Mar 2021 06:59:58 GMT

Hi @OrlandoPM

Try like below

If(Rank(Aggr(Sum(Quantity), Deno))=1, Sum(Total<Deno> Quantity))