Solved: Selecting fields in two columns as "OR" - Qlik Community

Report Inappropriate Content · ‎2009-08-25

I'm sure this has come up before, but searching for it in the forum is hard to do.

I have a table: COHORT_TABLE It contains three fields:

PatientID

DiagCode

MedcinID

I want to be able to select a PatientID based on if they have specific Diagnosis Codes OR MedcinIDs. For exmaple:

PatientID, DiagCode, MedcinID
1, 250.0
2, 250.0, 30479
3, , 30479

I want to be able to select Diagnosis 250.0 and MedcinID 30479 and get Patients 1, 2 and 3. But currently, I can only get patient 2.

I know I can do this with set analysis. I was trying to avoid it. I am considering re-designing the COHORT_TABLE to have only two columns, PatientID and CohortID, and combine DiagCode and MedcinID into that column (with a prefix to identify which code is which). That way, QV's natural "OR" functionality would work, since its all one column. I'm just afraid of confusing my users, if DiagCodes and MedcinIDs are all lumped together in one listbox.

Am I overlooking some option somewhere that makes this easier? I've looked around, but this wouldn't be the first time I've overlooked something obvious (that I just haven't encountered before).

Thanks in advance.

Report Inappropriate Content · ‎2009-08-26

I ended up going the macro route.


' SetCohort
'
SUB SetCohort
 ActiveDocument.Fields("CohortID").Clear

 set Cohort = ActiveDocument.Fields("CohortID").GetNoValues
 d=0 'Figure on no diagnosis

 'First, Load up Diag Codes
 if ActiveDocument.GetField("CohortDiag").GetValueCount(1) > 0 then
 set Diagnosis = ActiveDocument.fields("DCohortID").GetPossibleValues

 For i = 0 to Diagnosis.Count -1
 Cohort.Add
 Cohort.Item(i).text = Diagnosis.Item(i).text
 Cohort(i).IsNumeric = Diagnosis(i).IsNumeric
 next

 d=Diagnosis.Count
 end if

 'Then, Load up MedcinIDs
 if ActiveDocument.GetField("CohortMedcin").GetValueCount(1) > 0 then
 set Medcin = ActiveDocument.fields("MCohortID").GetPossibleValues

 for i = 0 to Medcin.Count - 1
 Cohort.Add
 Cohort.Item(d+i).text = Medcin.Item(i).text
 Cohort(d+i).IsNumeric = Medcin(i).IsNumeric
 next
 end if

 if Cohort.Count > 0 then
 ActiveDocument.Fields("CohortID").SelectValues Cohort
 end if

END SUB

Basically, CohortID is a field that I've combined both DiagIDs and MedcinIDs (with a prefix to make sure each is unique). CohortDiag and CohortMedcin are the pretty fields I let the user pick, and DCohortID and MCohortID are the grunt-work keys (with prefixes) to match up with CohortID.

The macro is triggered On Select for the pretty fields. Since people pretty much pick them, and leave it while going around the rest of the document, its not like it should be firing all the time.

It looks, almost, as if I took the opposite approach, and yours has the bonus of not using macros. But my users will be less confused if they can pick diags from a listbox of diags, and medcins from a listbox of medcins.

We'll see how it works at run-time with some real user's data on it. I don't if I'll have problems with any slow-downs. I can imagine it will be slow if a lot of items are chosen.

Thanks for your time and your ideas.

View solution in original post

Report Inappropriate Content · ‎2009-08-25

Hi Sally

I'm "Fairly" sure that the following will be what you need to use, I am using a load command as follows:

LOAD * FROM CCDW_QVW_REQUIREDFUNDS.QVD (qvd) WHERE MATCH(Inc_QVW_Name, 'InvRep');

In the above example, Inc_QVW_Name is a column in the QVD file, so in your case you should probably do something like

LOAD * FROM COHORT_TABLE WHERE MATCH(Diagnosis, 250.0) AND MATCH(MedicinID, 30479)

Hope this helps,

Report Inappropriate Content · ‎2009-08-25

Sorry, by "select" I meant select by listbox, not load script. My users must be able to select patient illnesses on the fly, not part of a filtering process during the load.

hector · ‎2009-08-25

As far I Know, values in the same list box are selected with an "OR" (excluding the AND parameter for certain type of tables), and between 2 or + listboxes the logic is an "AND"

maybe you need to considerate an alternative way of "query"

c ya

biester · ‎2009-08-25

Hi Sally,

what I can think of would be Advanced Search or simply put an expression like this in the search box:

=MedcinID like '30479' or DiagCode like '250*'

Would at least the result be what you intended?

Rgds,
Joachim

Report Inappropriate Content · ‎2009-08-25

Sally,

I have attached a possible solution using AGGR() in a stright table and island fields for the Diag and Medcin selection. You can see the "answer once you select the possible choices in the island ("OR") fields by clicking on "Yes" in the table. This will select the possible patients.

A few notes:

1. I currently have the island fields set to One and Only One, but you can probably code this in the aggr to work with more than one possible value.

2. I initially used the aggr() across all 3 fields, Patient, Diag, and Medcin, but just using Patient seems to work - at least in this example. Just understand that everything you aggr() by, will be selected when clicking on Yes.

By the way, be very careful about loading Diagnosis codes in QlikView. You're proably already aware, but these must be loaded in as text (on the initial load in script) or else similar numerics will be interpreted as the same.

Good luck,

Phil

johnw · ‎2009-08-26

Well, the only example of mine that I found uses set analysis to handle an OR between Customer and Product selections:

sum({<Customer=>+<Product=>}Revenue)

Any particular reason you want to avoid set analysis? Earlier version than 8.5? Concerns about performance? Concerns about complexity?

johnw · ‎2009-08-26

Phil's approach of field islands and simply using IF and OR works as well if you really want to avoid set analysis. For many purposes, you wouldn't need the aggr(). See attached charts and list box, none of which use aggr(). I suspect that for large data sets, it would be noticeably slower than the set analysis approach. Warning: version 9 set analysis has some performance problems that have been fixed in an internal patch, not yet released. If the set analysis approach seems slower than the IF, and you're on version 9, that is likely the reason.

Report Inappropriate Content · ‎2009-08-26

I had wanted to avoid set analysis because I will need to add the set analysis to ever single equation and chart across the whole QVW. And there's a lot of them. Which, if I think of it, is a bit of cut and pasting in all of the equations.

I've had some work with complicated set analysis, and I certainly appreciate how much faster they are than creating lists myself. By the nature of this document, I suspect a user wouldn't select more than 100 items in either DiagCode or MedcinID.

And, I just realized, with Phil's solution, I am still stuck changing each of my equations.

Basically, I want my user to be able to pick some patients using Medcin and Diagnosis, and then view all of the other metrics on the tabs based on that cohort of patients. So what I'm trying to do isn't limited to a chart. My scope is for the whole QVW.

Oh, I am anxiously awaiting the 9.0 patch release. I freaked the first time I pulled up a document with set analysis and most of the sets ceased to function.

Phil - thanks for the reminder on diagnosis codes. As it turns out, I have often combined the code with some alpha string, which has been forcing it to be text. But I will keep that in mind, if I ever grab the code straight up.

johnw · ‎2009-08-26

Ah, OK, so ideally this would somehow just WORK across EVERY object in your application, and not require complicated expressions throughout your whole application. That makes sense now.

My first thought would be a DATA representation of OR. The main problem that I see with it is that a large data set could easily explode into an unmanageably large OR table. Here's an example of that approach, though, in case your data set is small enough for it to work.

Edit: Heh, rereading, I see that YOUR first thought was also to use a data representation of OR. Let me take a closer look at your proposed solution.