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?
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
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
please share the excelfile!
Ok, i changed some classified information, but i think with this we can work
Hi Gabriel,
Sorry, but i need a script solution, to use a Google API on the result
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;
Exactly!
Could you please explain this concept that i didn't' understand? RangeSum(Peek('Rank'), 1)
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)
Thank You Frank