Expression Assistance - MAXROW()

[mykrobinson]

I need some help with forming an unfamiliar expressions (still pretty new to this). I have overcome some of my previous concerns in trying to craft an app for internal use and am now trying to create a patient view for when the doctors are doing their rounds (this is all to help them get information faster and not have to carry around a bunch of printed pages).

Anyways, the view I am working on, I want to display some data from the most recent hospital admission. There is a view that contains all patients, and another that is a slice, which displays patients who are currently "in hospital."

There is a secondary table that contains hospital admissions and that table contains refs to the primary key of the patient so that it can be related to the patient and a list of hospital admissions be tied to that record.

As I said, i want the In Hospital Detail record to show select generic patient info, which it does, but I want it to show the current room number of the patient. I "think" this can be done by adding a virtual column in the Patient table called Room Number or whatever, but i don't want to grab a list of every room the patient has ever been it, I only want it to display the most recent, or CURRENT room.

I think this can be accomplished with the virtual table and a MAXROW() expression but that's where I'm having trouble.
Is this the correct approach, and if so, how do I complete the expression to only return a single value with the most recent room based on the admission date?

Thank you, as always. You folks make a rookie feel quite welcome, and I am learning a lot with these questions. Definitely learning that I am trying to progress faster than the training materials I am working with, but these extracurricular education experiences are quite helpful to discover expressions that haven't been covered in the course material yet.

[graham_howe · Accepted solution]

You are on the right track, the expression 

MAXROW("admissions","Admission Date")

will return the key ([_encounterID]) of the record from the "admissions" table which has the latest [Admission Date]. You can therefore use that key to access the [Room] that the patient was last in. A LOOKUP() function would work like this:

LOOKUP(MAXROW("admissions","Admission Date"), "admissions", "_encounterID", "Room")

[mykrobinson]

Thank you! Now, before I just go and drop in the expression you made for me, I want to go and read the documentation for the LOOKUP() expression. Seems there are so many possibilities, and comments like yours are helping to get me much faster exposure to expressions that are more advanced than my current spot in the class. I realize that going about it this way can possibly be harmful, but I find that learning the basics and getting a little exposure to more while covering the basics is quite helpful!

[mykrobinson]

Just thinking out loud and documenting where I am, it helps me to go back and read this stuff.

I "think: i need to tweak the expression further because the lookup expression as written theoretically will not necessarily pull the record associated with that patient. I think [_THISROW] may need to be inserted somewhere to make sure that it pulls the row(s) from the Admissions table where the value _patientIdentifier is equal to the value of _patientIdentifier in the current row. Makes sense in my head, just need to figure out syntax, i think 🤔

[graham_howe]

You are absolutely correct, you will need to tweak to pull the value for the patient (I thought you were already pulling from a patient specific slice so excluded that). However the place to add the patient is not in the Lookup, but in the MAXROW. Something like this:

LOOKUP(
  MAXROW(
    "admissions",
    "Admission Date", 
    ([_patientIdentifier] = [_THISROW].[_patientIdentifier])
  ), 
  "admissions", 
  "_encounterID", 
  "Room"
)

[mykrobinson]

looks like we posted around the same time. I was CLOSE! WOuld you mind checking out what I had and explaining where i went wrong?

[mykrobinson]

This worked:

lookup(MAXROW("admissions","Admission Date",([_patientIdentifier]=[_THISROW].[_patientIdentifier])),"admissions","_encounterID","Room")

 I think my primary issue was the fact that i was not using brackets around certain column names, so it was not actually checking for the value of the columns. Does that sound right?

[mykrobinson]

This is what i have come up with. It's not producing an error and crashing the app but it's also not returning anything 🙂 Will need to revise this expression and make it behave 🙂

lookup(MAXROW("admissions","Admission Date",[Admissions]._patientIdentifier=[_THISROW]._patientIdentifier),"admissions","_encounterID","Room")