How to show only max values?

[OrlandoPM]

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

[Frank_Hartmann]

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;

[OrlandoPM]

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.

[MayilVahanan]

Hi @OrlandoPM 

Try like below

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