topic Re: Displaying max value in a table column in App Development

Displaying max value in a table column

GOKULAKANNAN — Fri, 08 Nov 2024 16:00:59 GMT

I have a table with columns employee id and their best attendence and current attendence, I want to display in a simple table, first column should be employee id and second column should be his best attendence and third will be current attendence,

In this best attendence will be increasing day by day until he takes leave,and if he takes leave then current attendence will come to 0 and start again.

Here I need to display distinct employee id and their best attendence and current one, and in this best attemdence should remain same and should not change for any filters, it should always display the maximum of the employee, where as current attendence should display the maximum on that particular date.

Input table:

Employee id. Best current

123. 48. 48

123. 48. 0

123. 48. 1

Output expectation:

Employee id. Best. Current

123. 48. 1

How can we achieve this

Re: Displaying max value in a table column

Qrishna — Fri, 08 Nov 2024 18:17:21 GMT

As you dont have date-timestamp for sorting and figuring out most current record, i used recno to fetch the most current record assuming the last record in the table is the most recent entered record. you can do like below:

//Backend Solution

Data:
LOAD *, recno() as sno inline [
Employee_id, current
123, 48
123, 0
123, 1
];

left join(Data)
LOAD Employee_id,
Max(current) as Best,
FirstSortedValue(current, -sno) as most_current
RESIDENT Data
GROUP BY Employee_id;

//Front End Solution:
Dim: Employee_ID

Measure1: Best = Max(total <Employee_id> current)
Measure2: current = only({$<sno={"$(=max(total <Employee_id> sno))"}>}current)

the most current record table is unaffected by any selections like you mentioned.

on the UI, there are couple other methods, if this works for you proceed with it.

Re: Displaying max value in a table column

GOKULAKANNAN — Fri, 08 Nov 2024 18:45:10 GMT

Thanks @Qrishna for the reply.

If we are using total then wont it show the same data for other employees, provided table as an example having only 1 emp. What if there are 100 employees. Am trying to do everything in frontend

Re: Displaying max value in a table column

Qrishna — Fri, 08 Nov 2024 19:20:43 GMT

it should wor but am not sure how yur real table is buillt, if it doesnt work in your table try: Measure2 =FirstSortedValue(current, -sno) . for your understanding i included another epmploee :

Re: Displaying max value in a table column

Kushal_Chawda — Sun, 10 Nov 2024 19:16:25 GMT

@GOKULAKANNAN If your current & Best are direct columns (not measure) then try below with dimension as EmpID

Data:
Load *
Inline [
EmpID, Best,Current
123,48,0
123,48,48
123,48,1

456,35,0
456,35,10
456,35,5 ];

Best = max(Best) Current = Min({<Current={">0"}>}Current)

Re: Displaying max value in a table column

GOKULAKANNAN — Mon, 11 Nov 2024 15:07:48 GMT

Data is like below,

Emp_id, emp_name, attendence, date

1 , gk, 1, 25/11/2024

1,gk, 2, 26/11/2024

1,gk,3, 27/11/2024

1,gk,0,28/11/2024

If am not selecting any filters, the result should show the recent value of attendence which is 0, else based on selection it should show, lets say if we are selecting 25 and 26 then 2 should be visible which is recent

Re: Displaying max value in a table column

Kushal_Chawda — Mon, 11 Nov 2024 15:12:17 GMT

@GOKULAKANNAN then below should work

=FirstSortedValue(distinct attendence,-date)