topic Re: Find Min Value from entire table in App Development

Find Min Value from entire table

vladimirkup — Tue, 17 Mar 2020 13:10:24 GMT

Hello,

if i have a simple table , for example like the one below with 3 columns (A,B,C) and 3 rows:

A | B | C

1 | 7 | 9

2 | 11 | 13

5 | 21 | 3

How do i find a field with lowest value from the entire table ? i know how to use MIN with Group BY but that would give me only a MIN value per column and i am looking for the minimum value of the entire table. so in ,y example that would be 1 (column A in first row)

Thanks in advance,

Vladimir

Re: Find Min Value from entire table

Wlad_Masi — Fri, 01 May 2020 21:23:40 GMT

Hi @vladimirkup ,

I would probably use the min function in each column, store in variables and compere them.
I am afraid we won't have a way to pick the lowest number of the table as one.

Re: Find Min Value from entire table

javiersassen — Fri, 01 May 2020 21:34:08 GMT

First question would be why do you want/need it? Second, does it have to be dynamic or is a static value fine?

Re: Find Min Value from entire table

vladimirkup — Sun, 03 May 2020 05:01:07 GMT

it's an intermediate calculation that will be used later. it's an dynamic value, the whole table will be updated on a daily basis.

Re: Find Min Value from entire table

vladimirkup — Sun, 03 May 2020 05:03:58 GMT

Hi,

Can you show please how to store min function from each column in a variable ? can you provide code example fro that ?

Thanks in advance

Re: Find Min Value from entire table

javiersassen — Mon, 04 May 2020 17:09:57 GMT

I might have a better solution:

OriginalTable: LOAD * INLINE [ RowId, A, B, C 1, 1, 7, 9 2, 2, 11, 13 3, 5, 21, 3]; MinValue: CrossTable(Column,Value) Load * resident OriginalTable;

In this way you have your original table, based on selections in your original table your min value changes. Your function to calculate the min value in the table would now be Min(Value).

Let me know if this helped!

Re: Find Min Value from entire table

vladimirkup — Wed, 06 May 2020 11:02:30 GMT

Wow, great idea, nice and easy, really helped me with that.

Just before i close this case can you show please how to store min function from each column in a variable ? That was your initial idea.

I will very appreciate that.

Re: Find Min Value from entire table

javiersassen — Wed, 06 May 2020 12:04:12 GMT

So first you create a variable, either in your script or in the edit sheets view (can only be done in unpublished dashboards)

SET vMinA = Min(A);

and then to user it in for instance a measure you have to evaluate the variable using $()

$(vMinA)

Re: Find Min Value from entire table

vladimirkup — Wed, 06 May 2020 13:20:22 GMT

Thanks !