Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
How can i get the 3 minimum values o Distancia?
Is this what you are expecting?
Script:
Table: LOAD CNPJ, RazaoSocial, Distancia_Google FROM [lib://Desktop/QS_Example.xlsx] (ooxml, embedded labels, table is Sheet1); NoConcatenate Final: Load CNPJ, RazaoSocial, Distancia_Google, If(CNPJ = Peek(CNPJ), RangeSum(Peek('Rank'), 1), 1) as Rank Resident Table where If(CNPJ = Peek(CNPJ), RangeSum(Peek('Rank'), 1), 1)<=3 order by CNPJ, Distancia_Google,RazaoSocial ; DROP Table Table;
maybe this:
Straight Table:
Dimension:
name
Expression:
=if(rank(sum(Distancia_.....))<= $(=max(aggr(rank(sum(Distancia_.....)),name))) and rank(sum(Distancia_.....))>= $(=max(aggr(rank(sum(Distancia_.....)),name)))-2 ,sum(Distancia_.....),Null())
Hi Frank,
I think that is a chart solution, i need a script.
In general:
LOAD min(Distancia, n) as MinDist
Resident mytable
Group By Distincia;
Where "n" is 1 for the lowest min, 2 is next min, etc.
-Rob
try this:
Table: LOAD * INLINE [ name, Distancia_... NAMORAL LANCHES, 30.40 J C L EMBALAGENS, 6.81 MARCO ZERO, 13.64 MARCOBRAS AMBIENTAL, 6.35 PIZZARIA 2 FELIX, 32.48 REFRIGERACAO BASTOS ANDRADE, 32.47 ] ; NoConcatenate Final: Load *,AutoNumber(Distancia_...) as Rank Resident Table Where AutoNumber(Distancia_...)<=3 order by Distancia_... asc; DROP Table Table
Hi Rob,
Thank you for your time, i saw your code and it works very good, but give me one value ,the 1°,2°, 3° etc minimun value.
There any function in script that give me the first 3 minimuns values, cause if not i thin the best solutiob will be something like:
min(Distancia, 1)&';'&min(Distancia, 2)&';'&min(Distancia, 3);
Again, thank you for your help
Hi Frank,
Unfortunately doesn't work cause brings the first 3 minimum values, but of all values and i need the first 3 minimum distancies group by CNPJ, but this is my fault i think that i don't explain very well my problem.
I have a table with several fields from companies, everyone have a Distance and a CNPJ (primary Key) besides others fields like name. So for each CNPJ i have something like 30 distancies i need only the first 3.
This is part of my code, sorry to not send a qvf.
Address: Load CNPJ,
LatLong, vicinity, name, Distancia_Google Resident PlaceId;
try like that. I have added Group Field (with value 1 or 2)
Table: LOAD * INLINE [ name, Group, Distancia_... NAMORAL LANCHES, 1, 30.40 J C L EMBALAGENS, 2, 6.81 MARCO ZERO, 1, 13.64 MARCOBRAS AMBIENTAL, 2, 6.35 PIZZARIA 2 FELIX, 1, 32.48 REFRIGERACAO BASTOS ANDRADE, 2, 32.47 Test1, 1, 22.4 Test2, 2, 4.65 ]; NoConcatenate Final: Load *, If(Group = Peek(Group), RangeSum(Peek('Rank'), 1), 1) as Rank Resident Table where If(Group = Peek(Group), RangeSum(Peek('Rank'), 1), 1)<=3 order by Group, Distancia_... ; DROP Table Table;
This gives you the 3 minimum values for each group!