Lookup other Row value from sort

Hey everyone!

I was wondering if someone could help with this particular issue.

I have a email template with the following formula, which gives me the Max value of the column [site outage].

<<INDEX(SORT(Canada Alerts[site outage], TRUE), 1)>>

This is great. but I am looking for the value of another column in that exact row. which is [site]. It is probably simple but i cant think of a formula?

Thanks!

Try:

LOOKUP(
  MAXROW("Canada Alerts", "site outage"),
  "Canada Alerts"
  "row key",
  "site"
)

replacing row key with the name of the Canada Alerts table’s key column.

See also:


1 Like

Thanks @Steve

I appreciate your prompt reply. That looks great, would i be able to use that formula to lookup the 2nd 3rd etc. from <<INDEX(SORT(Canada Alerts[site outage], TRUE), 1)>>

ie:
INDEX(SORT(Canada Alerts[site outage], TRUE), 2)
INDEX(SORT(Canada Alerts[site outage], TRUE), 3)

Hey,

just to add to this. I tried

<<LOOKUP(INDEX(SORT(Canada Alerts[site outage], TRUE), 2), “Canada Alerts”, “Issue #”,“site”)>>

But nothing came up.

Try what I suggested.

Hey @Steve

What you suggested works for the 1st maxrow. I trying to get consecutive rows. This is why i am using the INDEX and SORT function

Hey, just some background here. I am trying to get a email template to show top 10 “site outages”. I am having a difficult time getting the “start” filter to get the values I am looking for. Here is what I have, ive been messing around with it for awhile, but feel that i am somewhat close to a resolution.

<<Start:TOP(ORDERBY(Canada unplanned[issue #], [site outage], TRUE), 1)>>

<<INDEX(UNIQUE(Canada unplanned[Site]),1)>> <<INDEX(UNIQUE(Canada unplanned[Site outage]),1)>>kW
<<INDEX(UNIQUE(Canada unplanned[Site]),2)>> <<INDEX(UNIQUE(Canada unplanned[Site outage]),2)>>kW
<<INDEX(UNIQUE(Canada unplanned[Site]),3)>> <<INDEX(UNIQUE(Canada unplanned[Site outage]),3)>>kW
<<INDEX(UNIQUE(Canada unplanned[Site]),4)>> <<INDEX(UNIQUE(Canada unplanned[Site outage]),4)>>kW
<<INDEX(UNIQUE(Canada unplanned[Site]),5)>> <<INDEX(UNIQUE(Canada unplanned[Site outage]),5)>>kW
<<INDEX(UNIQUE(Canada unplanned[Site]),6)>> <<INDEX(UNIQUE(Canada unplanned[Site outage]),6)>>kW
<<INDEX(UNIQUE(Canada unplanned[Site]),7)>> <<INDEX(UNIQUE(Canada unplanned[Site outage]),7)>>kW
<<INDEX(UNIQUE(Canada unplanned[Site]),8)>> <<INDEX(UNIQUE(Canada unplanned[Site outage]),8)>>kW
<<INDEX(UNIQUE(Canada unplanned[Site]),9)>> <<INDEX(UNIQUE(Canada unplanned[Site outage]),9)>>kW
<<INDEX(UNIQUE(Canada unplanned[Site]),10)>> <<INDEX(UNIQUE(Canada unplanned[Site outage]),10)>>kW

<>

Try:

<<Start:TOP(ORDERBY(FILTER("Canada unplanned", TRUE), [site outage], TRUE), 10)>>
<<[Site]>>	<<[Site outage]>>kW
<<End>>

The problem with this is there are multiple rows of the same site. [site outage] is a virtual column, with an SUM for that particular site. When I do;

<<Start:TOP(ORDERBY(FILTER(“Canada unplanned”, TRUE), [site outage], TRUE), 10)>>
<<[Site]>> <<[Site outage]>>kW
<>

I get

Top 10 Outages by Site total (Unplanned)

TDSB - 550 Markham (Cedarbrae CI) 394

TDSB - 550 Markham (Cedarbrae CI) 394

35946 Creamery (Van Osch Farms B) - North Middlesex 250

TDSB - 10 Jamestown (Greenholme JMS) 236

TDSB - 86 Montgomery (Etobicoke CI) 217

TDSB - 86 Montgomery (Etobicoke CI) 217

PDSB - 7885 Kennedy (Turner Fenton SS) - Brampton 186

PDSB - 7885 Kennedy (Turner Fenton SS) - Brampton 186

PDSB - 7885 Kennedy (Turner Fenton SS) - Brampton 186

PDSB - 7885 Kennedy (Turner Fenton SS) - Brampton 186

I would not like to get duplicates. that is why I am trying the UNIQUE formula**

1 Like

Easy enough!

<<Start:TOP(ORDERBY(UNIQUE(FILTER("Canada unplanned", TRUE)), [site outage], TRUE), 10)>>
<<[Site]>>	<<[Site outage]>>kW
<<End>>

I tried that aswell.

same result;

Top 10 Outages by Site total (Unplanned)

TDSB - 550 Markham (Cedarbrae CI) 394kW
TDSB - 550 Markham (Cedarbrae CI) 394kW
35946 Creamery (Van Osch Farms B) - North Middlesex 250kW
TDSB - 10 Jamestown (Greenholme JMS) 236kW
TDSB - 86 Montgomery (Etobicoke CI) 217kW
TDSB - 86 Montgomery (Etobicoke CI) 217kW
PDSB - 7885 Kennedy (Turner Fenton SS) - Brampton 186kW
PDSB - 7885 Kennedy (Turner Fenton SS) - Brampton 186kW
PDSB - 7885 Kennedy (Turner Fenton SS) - Brampton 186kW
PDSB - 7885 Kennedy (Turner Fenton SS) - Brampton 186kW

Just for testing, what does this give?

<<Start:TOP(ORDERBY(UNIQUE(FILTER("Canada unplanned", TRUE)), [site outage], TRUE), 10)>>
<<[_ROWNUMBER]>>	<<[_THISROW]>>	<<[Site]>>	<<[Site outage]>>kW
<<End>>

"Error: Report ‘Alerts 2’ action ‘Action 1’ Body template. Expression ‘[_THISROW]’ is invalid due to: Unable to find column ‘_THISROW’, did you mean ‘Start’?.

Sigh. How about just this?

<<Start:TOP(ORDERBY(UNIQUE(FILTER("Canada unplanned", TRUE)), [site outage], TRUE), 10)>>
<<[_ROWNUMBER]>>	<<[Site]>>	<<[Site outage]>>kW
<<End>>

More of the same :frowning:

Top 10 Outages by Site total (Unplanned)

200 TDSB - 550 Markham (Cedarbrae CI) 394kW
9,027 TDSB - 550 Markham (Cedarbrae CI) 394kW
9,687 35946 Creamery (Van Osch Farms B) - North Middlesex 250kW
225 TDSB - 10 Jamestown (Greenholme JMS) 236kW
295 TDSB - 86 Montgomery (Etobicoke CI) 217kW
9,514 TDSB - 86 Montgomery (Etobicoke CI) 217kW
776 PDSB - 7885 Kennedy (Turner Fenton SS) - Brampton 186kW
6,105 PDSB - 7885 Kennedy (Turner Fenton SS) - Brampton 186kW
6,811 PDSB - 7885 Kennedy (Turner Fenton SS) - Brampton 186kW
6,920 PDSB - 7885 Kennedy (Turner Fenton SS) - Brampton 186kW

1 Like

Interesting! So you have the same (site, site outage) pairs in multiple rows.

Oh, as you said…

Oops.

Lemme feed the dogs, then I’ll be back.

Yes exactly!

Cant let those dogs go hungry.

the closet I got was;

<<Start:TOP(ORDERBY(Canada unplanned[issue #], [site outage], TRUE), 1)>>

then
<<INDEX(UNIQUE(Canada unplanned[Site]),1)>>
and so on.

but these were not in order. and all over the place. missing some sites;

Top 10 Outages by Site total (Unplanned)

TDSB - 550 Markham (Cedarbrae CI) 394kW
TDSB - 10 Jamestown (Greenholme JMS) 236kW
TDSB - 86 Montgomery (Etobicoke CI) 217kW
TDSB - 130 Lloyd Manor (John G Althouse MS) 180kW
OCDSB - 679 Deancourt (Fallingbrook ES) - Orleans 100kW
TDSB - 2335 Dufferin (Fairbank MS) 136kW
PDSB - 7885 Kennedy (Turner Fenton SS) - Brampton 186kW
TDSB - 70 Maxome (Cummer Valley MS) 27kW
PDSB - 2671 Sandalwood Pkwy East (Sandalwood Heights SS) - Brampton 158kW
PDSB - 251 McMurchy (Brampton Centennial SS) - Brampton 52kW
1 Like

Do you have a table of sites (matching the values in Canada unplanned[Site])? That would make this much easier.

OF COURSE!

Thats just makes sense. I will try adding a virtual column on that sheet

Putting the VC there will also substantially improve sync times by removing all those calculations from the larger table.