11 Replies Latest reply: Aug 26, 2009 11:26 PM by Sally Hurley RSS

    Selecting fields in two columns as "OR"

    Sally Hurley

      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.

       

        • Selecting fields in two columns as "OR"

          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,

            • Selecting fields in two columns as "OR"
              Sally Hurley

              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.

                • Selecting fields in two columns as "OR"

                  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

                  • AW:Re: Selecting fields in two columns as "OR"
                    Joachim Rogginer

                    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

                    • Selecting fields in two columns as "OR"
                      pjb

                      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

                        • Selecting fields in two columns as "OR"
                          John Witherspoon

                          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?

                            • Selecting fields in two columns as "OR"
                              John Witherspoon

                              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.

                                • Selecting fields in two columns as "OR"
                                  Sally Hurley

                                  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.

                                    • Selecting fields in two columns as "OR"
                                      John Witherspoon

                                      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.

                                        • Selecting fields in two columns as "OR"
                                          John Witherspoon

                                          I thought I could make your proposal work by creating list boxes for expressions if(Field='Diag',Code) and if(Field='Medcin',Code). But when I select a value in one, the other box goes gray since they're actually the same field. I can't select a value in both boxes. I can select both values if I just have a straight up list box for Code, but you're right that that would be inconvenient for the users. Hopefully there's a way around it, but it's not jumping out at me. In any case, I'll attach what I have in case someone wants to fiddle with it.

                                            • Selecting fields in two columns as "OR"
                                              Sally Hurley

                                              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.