Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rittermd
Honored Contributor

Join question

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.

4 Replies

Re: Join question

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
Honored Contributor

Re: Join question

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;

Employee
Employee

Re: Join question

‌an alternative to exists is the KEEP prefix

Keep ‒ Qlik Sense

Re: Join question

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)


Community Browser