4 Replies Latest reply: Oct 30, 2017 4:52 AM by Bill Markham RSS

    Join question

    Mark Ritter

      I have 2 excel files.

       

      One is a master list of people.

      The other contains medical claims for a period of time.

       

      There will be people in the master list that did not have any medical claims.  I don't want their records in the final data.

       

      There will be medical claims for people that are not in the master list of people.  I may want these claims in another file.  But not in the data in this dashboard.

       

      What I want to end up with is just a list of the people who had medical claims.

       

      I'm not quite sure on the best way to do this in the script.

        • Re: Join question
          Bill Markham

          First load the claims file.

           

          Then load the people file with a suitable Where Exists() clause

           

          This Dual & Exists – Useful Functions explains the Exists() function.

            • Re: Join question
              Mark Ritter

              I was trying to do that but something is not right.  Unfortunately the field that I need to match on is a text field (person's name).  One file is upper case and the other is lower case.  So I tried to convert them to Upper case and then do the where exists.  But I am doing something wrong.

              Here is my script.  I appreciate you taking a look.

               

              Optima:

              LOAD

                  mem_id,

                  Upper(mem_name) as Name,

                  claim_id,

                  seq,

                  adjust_cd,

                  adjust_cd_desc,

                  Incurred_Date,

                  AP_Date,

                  Admit_Date,

                  Discharge_Date,

                  "Class",

                  Claim_Provider,

                  Claim_Vendor,

                  Diag1,

                  Diag1_desc,

                  Diag2,

                  Diag2_desc,

                  proc_cd,

                  modifier,

                  proc_name,

                  Billed,

                  Paid,

                  Denied_Flag

              FROM [lib://OPTIMA/June 2017 HRRJ_Extract_PHI_201706 (1).xlsx]

              (ooxml, embedded labels, table is [Optima]);

               

               

               

               

              OOCTemp:

              LOAD

                  "DOC #",

                  Upper(Name) as Name,

              //    "CO days",

                  "CO Received",

                  "Current Location Name",

                  DOB,

                  Sex,

                  Race

              FROM [lib://OOC/*.xlsx]

              (ooxml, embedded labels, table is Intake.rdl);

               

               

              OOC:

              Load *

              Resident OOCTemp

              Where Exists (Name,Name);

              //Drop Table OOCTemp;

                • Re: Join question
                  Bill Markham

                  When you load into OOCTemp you will  load all the Name values, so when you load into OOC the Where Exists on it will find and load all the Name values as they all will exist.


                  Often with this type of scenario I would :

                  • Load OCDTemp
                  • Store OCDTemp into a qvd
                  • Drop OCCTemp
                  • Load OOC from the qvd using the Where Exists


                  Also for the Where Exists I'd simply use :

                  • Where Exists (Name)


                   

              • Re: Join question
                Damian Waldron

                an alternative to exists is the KEEP prefix

                 

                Keep ‒ Qlik Sense