Max by State - Qlik Community

coddra_6 · ‎2013-12-23

Hey,

I have the next case:

I have the sales of many products by state y I wanna know which product is the best selling by each state. I have the next table:

State Producto Sales

===== ======== ======

New York Cookie 1500

New York Soda 890

California Chocolate 2100

California Candy 2015

California Cookie 2085

And I wanna got the next table:

State Producto Sales

===== ======== ======

New York Cookie 1500

California Chocolate 2100

Hope your help!!

Thanks!!

swuehl · ‎2013-12-23

Create a chart with dimension State, then two expressions

=FirstSortedValue(Producto, -Sales)

=max(Sales)

qlikpahadi07 · ‎2013-12-24

May be this help you

coddra_6 · ‎2013-12-24

Yeah!! This helps!!

And if I wanna make it in the Script Editor??

maxgro · ‎2013-12-24

you have to change column name, I started from your post http://community.qlik.com/thread/102682

Tab:

LOAD * INLINE [

Departamento, Producto, Ventas

Ica, Galleta, 1500

Ica, Gaseosa, 890

Lima, Chocolate, 2100

Lima, Caramelo, 2015

Lima, Galleta, 2085

];

Tab2:

NoConcatenate load Departamento, Producto, Ventas

where Flag=1;

NoConcatenate load

Departamento, Producto, Ventas, if(Peek(Departamento)<>Departamento or IsNull(Departamento), 1, 0) as Flag

resident Tab

order by Departamento, Ventas desc;

drop table Tab;

qlikpahadi07 · ‎2013-12-24

here is from Script might be helpful

Merry Christmas !!!!