Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
AtilaH
New Contributor III

Checking if a multiple sub strings are found in a string

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

1 Solution

Accepted Solutions

Re: Checking if a multiple sub strings are found in a string

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

View solution in original post

4 Replies

Re: Checking if a multiple sub strings are found in a string

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

View solution in original post

AtilaH
New Contributor III

Re: Checking if a multiple sub strings are found in a string

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
New Contributor III

Re: Checking if a multiple sub strings are found in a string

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

AtilaH
New Contributor III

Re: Checking if a multiple sub strings are found in a string

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