Checking if a multiple sub strings are found in a string

[AtilaH]

Hi,

I have the following problem that I ma hoping somebody has had before and could help me with, the tables are large and there are many variations based on active ingredient and Disease,

I have the following Tables

Exclusion table:

Disease                   ATCLEVEL5                    PREPERATION EXCLUSIONS

ADDISONS             H02AA02                        INJ,SYR,POI 

Drug table:

UNiQUE ID       ATCLEVEL5         PREPERATION                     

3212451243       H02AA02             INJ

1231351522       H02AA02             CAP

I would like to generate a table that calculates whether a drug can be included or excluded based on the first tables exclusion criteria

Straight table output:

UNIQUE ID       ATCLEVEL5         INCLUDED/EXCLUDED

3212451243       H02AA02             EXCLUDED

1231351522       H02AA02             INCLUDED

Thanks in advance for the help

Regards

Atila

[kaushiknsolanki · Accepted solution]

Hi,

Try this.

Load  ATCLEVEL5&Exlusions as Exlude;

Load Disease ,  ATCLEVEL5 ,Subfield([PREPERATION EXCLUSIONS,]',') as Exlusions

From XYZ;

Load UNiQUE ID , ATCLEVEL5 ,PREPERATION, If(Exists(Exclude,ATCLEVEL5&PREPERATION),'Exluce','Include') as Newfield From PQR;

Regards,

Kaushik Solanki

[AtilaH]

Hi Kaushik

I ran the following script,

Load ATCLEVEL5&Exclusions as Exclude;

LOAD
Disease ,
ATCLEVEL5 ,
Subfield([Preperation exclusions],',') as Exclusions
FROM
[I:\Drug file\Qliksug.xlsx]
(ooxml, embedded labels, table is [Exclsusion table]);

LOAD [UNiQUE ID],
ATCLEVEL5,
PREPERATION,
If(Exists(Exclude,ATCLEVEL5&PREPERATION),'Exluce','Include') as Newfield
FROM
[I:\Drug file\Qliksug.xlsx]
(ooxml, embedded labels, table is [Drug tableUNiQUE ID]);

The exclusion doesn't seem to match the desired flags

I will Attach the model for convenience

[AtilaH]

Hi, Just had to tweek a few things and it worked perfectly thank you

[AtilaH]

Hi, there is a slight problem, when I add another disease that has the same ATC5Level that might be included , it is exluded