Get a Range of a Field

[eduardo_dimperio]

Hi,

How can i get the 3 minimum values o Distancia?

[Frank_Hartmann]

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())

[eduardo_dimperio]

Hi Frank,

I think that is a chart solution, i need a script.

[rwunderlich]

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

[Frank_Hartmann]

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

[eduardo_dimperio]

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  

[eduardo_dimperio]

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.

[Frank_Hartmann]

CNPJ?

Not clear to me what you want to achieve. Please describe in more detail and provide; if possible; a sample qvf

[eduardo_dimperio]

This is part of my code, sorry  to not send a qvf.

Address:
Load
CNPJ,
LatLong,
vicinity,
name,
Distancia_Google
Resident PlaceId;

[Frank_Hartmann]

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!