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

Checking if a multiple sub strings are found in a string

AtilaH — Sat, 16 Nov 2024 20:20:28 GMT

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

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

kaushiknsolanki — Wed, 31 Jul 2019 13:23:53 GMT

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

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

AtilaH — Thu, 01 Aug 2019 03:20:04 GMT

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

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

AtilaH — Thu, 01 Aug 2019 03:47:33 GMT

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

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

AtilaH — Thu, 01 Aug 2019 04:15:44 GMT

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