Expression Not Yielding Expected Results, Need Hel...

mykrobinson · 01-03-2023 12:24 PM

I created a virtual column this morning to make a bit of a header for the purpose of grouping patients in a certain view based on dialysis location (thanks, @Aurelien , for the assist). It is/was working but i noted that some patients didn't have a proper location documented in their dialysis schedule.

Those patients are properly grouped, but the header for the group simply showed a blank. I came up with this expression:

IF(
ISBLANK(
LOOKUP([_THISROW].[_patientIdentifier], "Dialysis","_patientIdentifier","Dialysis Location")),
"Unknown",
LOOKUP([_THISROW].[_patientIdentifier], "Dialysis","_patientIdentifier","Dialysis Location")
)

when i test the expression, the results show what i am expecting:

it actually shows "Unknown" if the location is blank.

what have i done wrong?

Aurelien

Hi @mykrobinson

I think your expression is correct.

However, I would dig on the dialysis location, in the DIALYSIS table. Is it possible that the location may be empty for some reason?

mykrobinson

that's the thing, just as with the test, i would expect the grouping head to show "Unknown" if the location is blank, but its not happening. The test button yields the result i expect to show up

Aurelien

It seems it is not considering it as blank. If it was, I would expect to see either "(blank)" or "Unknown".

Would it be possible that you have the value with just a single space instead a blank in the cell value, like " "?

mykrobinson

thinking out loud, but it seems the easiest fix for this is for the providers to correct the locations for these patients 😂

I mean, dialysis does have to happen somewhere 🤔

Aurelien

yes 🤣

you may want to tick the "require" option for the location. No more trouble 🙂

AleksiAlkio

I would also check that the option Settings > Data > Legacy > Blank value comparison mode is set as "Consistent"

mykrobinson

just checked and it is already set to consistent.

Steve

@mykrobinson wrote:

I came up with this expression:

More efficient:

ANY(
  LIST(
    LOOKUP(
      [_THISROW].[_patientIdentifier],
      "Dialysis",
      "_patientIdentifier",
      "Dialysis Location"
    ),
    "Unknown"
  )
  - LIST("")
)

Or potentially even:

ANY(
  LIST()
  + [Related Dialysis][Dialysis Location]
  + LIST("Unknown")
  - LIST("")
)

@mykrobinson wrote:

what have i done wrong?

I'd guess you aren't grouping by the virtual column.

mykrobinson

It's definitely grouped by the virtual column:

I'll try your method above and see if it pulls "Unknown"

Forgive me for not getting it, but if it shows "Unknown" when testing the formula, i still cannot understand why its not already pulling in the view.

Steve

What is the column type for Dialysis Location?

mykrobinson

sorry for the delayed response. It's Text

Expression Not Yielding Expected Results, Need Help Troubleshooting