Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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.
Hi @OrlandoPM
Try like below
If(Rank(Aggr(Sum(Quantity), Deno))=1, Sum(Total<Deno> Quantity))