Lookup other Row value from sort

BenH · 04-27-2020 12:36 PM

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!

Steve

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:

BenH

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)

BenH

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.

Steve

Try what I suggested.

BenH

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

BenH

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

<>

Steve

Try:

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

BenH

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

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

TDSB - 10 Jamestown (Greenholme JMS) 236

TDSB - 86 Montgomery (Etobicoke CI) 217

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

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

Steve

Easy enough!

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

BenH

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

Steve

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>>

BenH

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

Steve

Sigh. How about just this?

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

BenH

More of the same

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

Steve

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.

BenH

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

Steve

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

BenH

OF COURSE!

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

Steve

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

Steve

Now I gotta feed the cats. I’ll check back in in a bit, but so long as you get that VC moved, I’m pretty sure the rest just drops in place for you.