3 Replies Latest reply: Aug 1, 2017 1:06 PM by Todd Buss RSS

    Data Loading: create a value when one-to-many table comparison finds no match

    Todd Buss

      Pardon my lack of database parlance.  I think I have a pretty basic problem, but I don't know how to search for a solution.

      I have two tables: patient number and diagnosis codes&sequence.  Each patient could have between zero and nine diagnoses.  I'm able to load and count the values just fine, but i'm having trouble identifying all the patients with zero matches on the diagnosis code&sequence table.  They turn up as null in my visual, but null is not value that can be selected in QlikSense.

       

      I don't think this situation warrants the use Applymap() because of the one-to-many relationship.    Please correct me if i'm wrong. 

       

      Here's a simplified version of what I'm working with:

       

      [patient table]

      Patient_Number

      100

      101

      102

       

      [Diagnosis Table]

      Patient_Number, Diagnosis, Sequence

      100,"depression",1

      100,"anxiety",2

      // note patient 101 has no diagnoses.

      102,"depression",1

       

      [Desired Result Table]

      Patient_Number,Number_of_Diagnoses

      100,2

      101,0

      102,1

       

      Thanks in advance for any helpful tips.

       

      --Todd