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?
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?
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
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 " "?
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 ๐ค
yes ๐คฃ
you may want to tick the "require" option for the location. No more trouble ๐
I would also check that the option Settings > Data > Legacy > Blank value comparison mode is set as "Consistent"
just checked and it is already set to consistent.
@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.
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.
What is the column type for Dialysis Location?
sorry for the delayed response. It's Text
User | Count |
---|---|
27 | |
26 | |
22 | |
21 | |
21 |