Creating a table to exclude data out a single field based on multiple fields

[AtilaH]

Hi, 

I have some experience with Qlikview, I just need some help with the following

I have table of product data ( Tabel 1) , I  have created a table (Table 2 ) that links to that (Table 1) based on  field ATC and I need to perform certain exclusions based on requirements in the second table. I have attached a small set of the data with examples of the tables below. (There are multiple disease in the attached document, if it was only one disease then i could use set analysis, but since there are over 200 diseases in the complete dataset I don't think set analysis would work? Maybe I am thinking about it all wrong)

Thanks in advance Atila

Table 1

Unique Identifier

ATC

Presentation

HYPERTENSION:

883817007

MEDSOL CARDIOPLEGIC INDUCTION

B

BLOOD AND BLOOD FORMING ORGANS

B05

BLOOD SUBSTITUTES AND PERFUSION SOLUTIONS

B05X

I.V. SOLUTION ADDITIVES

B05XA

Electrolyte solutions

B05XA01

INF

1

Y

Y

HYPERTENSION:

894290002

MEDSOL CARDIOPLEGIC MAINT

B

BLOOD AND BLOOD FORMING ORGANS

B05

BLOOD SUBSTITUTES AND PERFUSION SOLUTIONS

B05X

I.V. SOLUTION ADDITIVES

B05XA

Electrolyte solutions

B05XA01

INF

1

Y

Y

HYPERTENSION:

718369001

NEPRESOL POWDER (SECT 21)

C

CARDIOVASCULAR SYSTEM

C02

ANTIHYPERTENSIVES

C02D

ARTERIOLAR SMOOTH MUSCLE AGENTS ACTING ON

C02DB

Hydrazinophthalazine derivatives

C02DB02

INJ

5

Y

Y

HYPERTENSION:

732052017

HYPOTONE

C

CARDIOVASCULAR SYSTEM

C02

ANTIHYPERTENSIVES

C02A

ANTIADRENERGIC AGENTS CENTRALLY ACTING

C02AB

Methyldopa

C02AB01

TAB

500

Y

Y

HYPERTENSION:

761400028

SANDOZ HYDRALAZINE

C

CARDIOVASCULAR SYSTEM

C02

ANTIHYPERTENSIVES

C02D

ARTERIOLAR SMOOTH MUSCLE AGENTS ACTING ON

C02DB

Hydrazinophthalazine derivatives

C02DB02

TAB

500

Y

Y

HYPERTENSION:

785962026

MYLAN METHYLDOPA

C

CARDIOVASCULAR SYSTEM

C02

ANTIHYPERTENSIVES

C02A

ANTIADRENERGIC AGENTS CENTRALLY ACTING

C02AB

Methyldopa

C02AB01

TAB

500

Y

Y

Table 2

Disease	ATC	Presentation Exclusion
HYPERTENSION:	B05XA01	INF,INJ,SUS,SYR
HYPERTENSION:	C02AB01	INF,INJ,SUS,SYR
HYPERTENSION:	C02CA04	INF,INJ,SUS,SYR
HYPERTENSION:	C02DB02	INF,INJ,SUS,SYR
HYPERTENSION:	C03AA03	INF,INJ,SUS,SYR
HYPERTENSION:	C03BA11	INF,INJ,SUS,SYR
HYPERTENSION:	C03CA01	INF,INJ,SUS,SYR
HYPERTENSION:	C03DA01	INF,INJ,SUS,SYR
HYPERTENSION:	C08CA01	INF,INJ,SUS,SYR
HYPERTENSION:	C08CA05	INF,INJ,SUS,SYR
HYPERTENSION:	C08DA01	INF,INJ,SUS,SYR
HYPERTENSION:	C09AA01	INF,INJ,SUS,SYR
HYPERTENSION:	C09AA02	INF,INJ,SUS,SYR
HYPERTENSION:	C09AA03	INF,INJ,SUS,SYR
HYPERTENSION:	C09AA04	INF,INJ,SUS,SYR
HYPERTENSION:	C09BA03	INF,INJ,SUS,SYR

[mato32188]

Hi Atila,

if you want to exclude values from table 1 based on ATCLevel5 field from table 2, try:

table2:
LOAD Disease,
ATCLevel5,
[Presentation Exclusions]
FROM YOUR FILE;

NoConcatenate 
table1:
LOAD Disease,
Unique,
[Drug Name],
ATC1,
[ATC1 Desc],
ATC2,
[ATC2 Desc],
ATC3,
[ATC3 Description],
ATC4,
[ATC4 Description],
ATCLevel5,
Presentation,
Packsize,
[Chronic Disease indicator],
[Acute formualry flag],
Combined,
[Preperation Exclusions]
FROM YOUR FILE where not Exists(ATCLevel5);

drop table table2;

 BR

Martin

[AtilaH]

Hi, Martin

Thanks for the response

I do but then, I need to able to also exclude based on multiple "Preparations"held in Table 2 column 3 aswell?

Regards

Andre

[mato32188 · Accepted solution]

Hi,

does it help to include ATC&Preparations key in where exists?

Thanks.

Martin