Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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;
an alternative to exists is the KEEP prefix
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 :
Also for the Where Exists I'd simply use :