Filter, Filter then MAXX, or something like that.

[SearchKnowledge]

Dear kind sir/madam,

I hava a nice challenge for you:

KEY	SEQUENCE	TIME	RESULT
SUP-1111	22	6
SUP-1111	23	0,5
SUP-1111	24	3	3
SUP-2222	30	5	5
SUP-2222	31
SUP-2222	32
SUP-3333	22	2
SUP-3333	23	9
SUP-3333	24
SUP-3333	25
SUP-3333	26	8
SUP-3333	27	4,5	4,5

As you can see I got a table with 3 columns. The first column specify the keys. The second column specify the sequence for each row related to the key. The sequence is not unique for the whole table, but is unique per key. The third column shows the time that is related to each sequence, sometimes it is filled and sometimes not.

What I need is to get column with the result. This result is only one value per distinct key. It is not always related to the highest sequence, because sometimes the highest sequence is empty. It also is NOT the highest time. It is the most recent time from the last sequence that has a time filled in. I hope the table above makes it clear.

I hope you can help me, Thanks in advance 🙂

[SearchKnowledge]

@amitchandak perfect. 10/10 thank you

[amitchandak · Accepted solution]

@SearchKnowledge , Create a new column like

result =
var _max = maxx(filter(Table, not(isblank([Time])) && [key] =earlier([Key])),[Sequence])
return
maxx(filter(Table, [Sequence] =_max && [key] =earlier([Key])),[Time])

[SearchKnowledge]

@amitchandak I am trying to understand how this works. But what is var_max exactly returning? A true or false statement or a table??