topic Re: Showing Differences in 2 tables in QlikView

Showing Differences in 2 tables

Tue, 20 Jan 2015 14:28:05 GMT

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

Re: Showing Differences in 2 tables

nirmalraj_kj — Tue, 20 Jan 2015 14:33:58 GMT

can you post the sample application or data

Re: Showing Differences in 2 tables

alexandros17 — Tue, 20 Jan 2015 14:37:14 GMT

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

Re: Showing Differences in 2 tables

Anonymous — Tue, 20 Jan 2015 14:39:41 GMT

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

Re: Showing Differences in 2 tables

Tue, 20 Jan 2015 15:03:26 GMT

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

Re: Showing Differences in 2 tables

alexandros17 — Tue, 20 Jan 2015 15:38:09 GMT

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;

Re: Showing Differences in 2 tables

maxgro — Tue, 20 Jan 2015 18:10:56 GMT

add a flag (bold) to claims detail

MapPharmacyRAMSNo:

Mapping LOAD

PharmacyRAMSNo, 'Yes'

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

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);

Re: Showing Differences in 2 tables

Tue, 20 Jan 2015 18:37:15 GMT

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;

Re: Showing Differences in 2 tables

Wed, 21 Jan 2015 11:12:08 GMT

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.

Re: Showing Differences in 2 tables

paymydoctorpage — Fri, 21 Mar 2025 05:45:06 GMT

there are the claims details of all Doctors......