Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis using multiple selections in one column to affect table contents

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.

1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

5 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

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

Anonymous
Not applicable
Author

Oleg,

If you sneak a = into the dollar expansion it should work:

sum({$<FIELD1 = {$(=concat('"' & FIELD2 & '"', ',' ))} >} AMOUNT1)

Not applicable
Author

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:

  • Both work exactly the same when there are selections made on the source field (FIELD2 in Oleg's example)
  • Using Concat, you can specify if you would like to enclose each element in quotes
  • Both options have the ability to specify the delimiter
  • The biggest difference is, using concat returns ALL field values when there is no selection made on the source field and using GetFieldSelections returns NO values when there are no selections made on the source field

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

Not applicable
Author

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.