Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Greetings,
I have two tables, that are not joined (for a variety of reasons). They are:
PatientTable: PatientID, PatDiagCode
DiagTable: DiagCode, DiagDescription
I have a listbox on DiagCode. I want to count PatientIDs of patients that have a PatDiagCode that matches what I've selected in DiagCode. I started with:
count( distinct { $<PatDiagCode= {$(=DiagCode)}>} PatientID)
But obviously that doesn't work (unless I've selected only 1 DiagCode). I've tried a number of different ways, but I can't seem to get it to work. I know, if all else fails, I can concatenate DiagCode into a variable, and use that as a list, but I'd really like to avoid that if possible.
Thanks.
Sally,
this can be solved with function concat() - you concatenate the selected values, enclosed in double quotes, and separated by a coma. The resulting list can be used in Set Analysis.
For some reason, I couldn't make the $-expansion work with concat() - could be something I'm missing in the syntax, so I had to use 2 variables in order to generate the list and pass it to Sales Analysis. There might be a more elegant solution...
See attached sample.
Oleg
Sally,
this can be solved with function concat() - you concatenate the selected values, enclosed in double quotes, and separated by a coma. The resulting list can be used in Set Analysis.
For some reason, I couldn't make the $-expansion work with concat() - could be something I'm missing in the syntax, so I had to use 2 variables in order to generate the list and pass it to Sales Analysis. There might be a more elegant solution...
See attached sample.
Oleg
Sally,
Would something like this work?
Count( {$< PatDiagCode={$(=GetFieldSelections(DiagCode) )} >} DISTINCT PatientID)
This function only returns values when there are selections made. I hope this works for what you're doing.
sjprows
Oleg,
If you sneak a = into the dollar expansion it should work:
sum({$<FIELD1 = {$(=concat('"' & FIELD2 & '"', ',' ))} >} AMOUNT1)
Sally,
Using Oleg's Example as a base I also implemented the "GetFieldSelections" option to see what the difference would be. Here's what I found:
I have attached a sample here for you to play with. I was interested in this question also, which is why I dug further into it. Go ahead and clear the selections on FIELD2 and you'll see what I am talking about.
Hope this helps
sjprows
Thanks, guys. These are really great ideas.
sjprows -- I love GetFieldSelections. Now I'm itching to do something that uses that -- its so simple! Unfortunately, I don't actually "select" DiagCode -- I have the user select the diagnosis description, and therefore, this doesn't work for me. However Oleg/jsn's concat logic does work, even when DiagCode hasn't been directly selected.
This seems to do the trick. Thanks for the input.