Showing Differences in 2 tables

[#1]

Good afternoon

I have a number of files that are in a Qliklview model. In one table there are a bunch of doctors names with the area they reside in. In a seperate file there are the claims details of all Doctors. I want to select all the Doctors who are in the second table but are not int he first. This would mean that the field "residearea" would be blank. Could someone suggest how to solve this. I was looking at making a list isnull or <> 0 but this seemed to only look at the one table and so showed all the data.

Regards

Neil

[nirmalraj_kj]

can you post the sample application or data

[alexandros17]

Surely there is one or more common fields in the two tables that links them, let's suppose this field is DoctorID.

Load DoctorID as ID_to_Exclude resident SecondTable;

Load * resident first table where not exists(ID_to_Exclude, DoctorID);

let me know

[Anonymous]

First do right or left join in script as required. Then use len(residearea)=0 in your chart condition.

[#5]

Good afternoon

I have attached the 2 files I am using. In the doctor residential area file, I have the fields PharmacyRAMSNo, PharmacyName, ActiveInterum, ActiveManaul and  Province. In the claims details file I have a number of fields but the field that matches to PharmacyRAMSNo in doctor residential area is DoctorsBHF. I want to be able to produce a table or a chart with the data from the Claims details file where the DoctorsBHF number is not equal to any of the values in the PharmacyRamsNo in in the doctor residential area file.

Thank you    

[alexandros17]

This is the code you need

RES_OK:
LOAD PharmacyRAMSNo,
PharmacyName,
ActiveInterum,
ActiveManaul,
Province
FROM
[Doctor residential area.xlsx]
(ooxml, embedded labels, table is Sheet1);

CLA_OK:
LOAD DoctorsBHF
FROM
[claims details.xlsx]
(ooxml, embedded labels, table is TB04_20150120_164519)
Where Exists(PharmacyRAMSNo, DoctorsBHF);

DROP Table RES_OK;

CLA_OK_OK:
LOAD MembershipNo,
BeneficiaryDependentCode,
DateServiceStarted,
DoctorsBHF,
FeeCharged,
Benefit
FROM
[claims details.xlsx]
(ooxml, embedded labels, table is TB04_20150120_164519)
Where not Exists(DoctorsBHF, DoctorsBHF);

DROP Table CLA_OK;

[maxgro]

add a flag (bold) to claims detail

MapPharmacyRAMSNo:

Mapping LOAD

PharmacyRAMSNo, 'Yes'

FROM [Doctor residential area.xlsx] (ooxml, embedded labels, table is Sheet1);

B:

LOAD MembershipNo, BeneficiaryDependentCode, DateServiceStarted,

DoctorsBHF, FeeCharged, Benefit,

ApplyMap('MapPharmacyRAMSNo', DoctorsBHF, 'No') as [Exists PharmacyRAMSNo]

FROM [claims details.xlsx] (ooxml, embedded labels, table is TB04_20150120_164519);

[#8]

use following code in your script or find a sample application in attachment

Tab:

LOAD PharmacyRAMSNo as [DoctorsBHF],

    PharmacyName,

    ActiveInterum,

    ActiveManaul,

    Province

FROM

[Doctor residential area.xlsx]

(ooxml, embedded labels, table is Sheet1);

Tab2:

LOAD MembershipNo,

    BeneficiaryDependentCode,

    DateServiceStarted,

    DoctorsBHF,

    FeeCharged,

    Benefit

FROM

[claims details.xlsx]

(ooxml, embedded labels, table is TB04_20150120_164519)

Where not Exists(DoctorsBHF);

drop table Tab;

EXIT Script;

[#9]

Thank you this does seem to show me some of what I need.

I may have not expanded fully on what I need to do. I would like to show all the lines in the claims details table where the DoctorsBHF does not equal PharmacyRamsNo. At the moment the table seems to show one record per DoctorsBHF but there could be multiple records with the same DoctorsBHF value.