Get a Range of a Field

[eduardo_dimperio]

Hi,

How can i get the 3 minimum values o Distancia?

[Frank_Hartmann]

can you upload the excelfile?

[Gabriel]

Hi,

Are you looking to achieve min 3 from the script or UI using Expression?

There's a quick way in the UI.

If you're using table as chart object, add your DIM and your expression.  Then under Dimension expand the Column, then you'll see Limitation, this works just like QlikView Dimension Limits. In Qlik Sense under Limitation, there's a drop-down, select Fixed Number, then you'll see Top & Bottom tabs, select Bottom then enter 3 in the fx space below. This should give you what you want to achieve.

Let me know if it helps

[eduardo_dimperio]

Hi Frank,

I did like you example and get this result:

Without Rank:

NoConcatenate
Final:
Load
CNPJ,
RazaoSocial,
Distancia_Google
Resident PlaceId_CNPJ
;
DROP Table PlaceId_CNPJ;


Exit Script;

Like you can see i select just one CNPJ with several Distancia_Google to better understand.

Now with Rank:

NoConcatenate
Final:
Load
CNPJ,
RazaoSocial,
Distancia_Google,
If(CNPJ = Peek(CNPJ),
RangeSum(Peek('Rank'), 1), 1) as Rank 
Resident PlaceId_CNPJ
where If(CNPJ = Peek(CNPJ), RangeSum(Peek('Rank'), 1), 1)<=3 order by CNPJ, Distancia_Google
;

DROP Table PlaceId_CNPJ;

Exit Script;

retrieve 1 and not the 3 minimum values

[Frank_Hartmann]

please share the excelfile!

[eduardo_dimperio]

Ok, i changed some classified information, but i think with this we can work

[eduardo_dimperio]

Hi Gabriel,

Sorry, but i need a script solution, to use a Google API on the result

[Frank_Hartmann · Accepted solution]

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;

[eduardo_dimperio]

Exactly!

Could you please explain this concept that i didn't' understand? RangeSum(Peek('Rank'), 1)

[Frank_Hartmann]

its just the cumulative sum.

if the condition CNPJ = Peek(CNPJ) is met, then  the formula starts with 1 and adds 1 untill 

CNPJ <> Peek(CNPJ) . this fact you can use in the where clause to restrict the data (Rank<=3)

[eduardo_dimperio]

Thank You Frank