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

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

Thu, 30 Jul 2009 23:36:01 GMT

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.

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

Oleg_Troyansky — Fri, 31 Jul 2009 00:09:07 GMT

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

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

Fri, 31 Jul 2009 04:44:37 GMT

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

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

Anonymous — Fri, 31 Jul 2009 09:07:14 GMT

Oleg,

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

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

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

Fri, 31 Jul 2009 20:38:51 GMT

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

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

Fri, 31 Jul 2009 21:41:20 GMT

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.