Join question

[rittermd]

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.

[Anonymous]

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.

[rittermd]

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;

[Anonymous]

‌an alternative to exists is the KEEP prefix

Keep ‒ Qlik Sense

[Anonymous]

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)