topic Creating flags from multiple tables into a single table in QlikView

Creating flags from multiple tables into a single table

Tue, 10 May 2011 15:45:55 GMT

Hi,

I have three tables within a SQL database, which relates to individuals

The master table holds records distinct to the %KEY_CARS

The Drugs table holds up to 7 records per %KEY_CARS.

The Observations table holds up to 3 records per %KEY_CARS.

I need to set a flag for each patient / record within the master table where the patient recived a 'care bundle'.

For example

obs seq number is 1 and a valid pain score

AND

obs seq number is 2 and a valid pain score

AND

a drug code for that patient / record shows 'ASP'

AND

a drug code for that patient / record shows 'GTN'

AND

a drug code for that patient i/ record shows either 'NOO' or 'MOR'

I can create indiviudal flags in each of the tables e.g. showing if the drug code is ASP then return a 1 etc

I have tried to join the tables togther but it links the 3 rows from the obs table with the 7 rows in the drugs table with the single row in the master table, but I want to have a single row instead.

The sample attached shows what I have managed so far but with limited success. I think what I need to do is link all of teh tables together, create the flag and then drop the tables I no longer need (there are other fields not mentioned above from the master table that I still need to use later on).

Hope this makes sense, its been quite an afternoon getting my head around this.

Phil

Re: Creating flags from multiple tables into a single table

nagaiank — Tue, 10 May 2011 18:26:54 GMT

If I understand your task correctly, I will restate it as follows:

1. There is a table of Keys:

Key

2. There is a table of multiple values for each Key:

Key Count Value

A 1 A1

A 2 A2

A 3 A3

B 1 B1

B 2 B2

B 3 B3

You want to combile these two tables and get the following table:

Key Value1 Value2 Value3

A A1 A2 A3

B B1 B2 B3

If my understanding is correct, you can get the result by multiple left joins as shown in the attached example.

Hope you can extend this to your application.

Creating flags from multiple tables into a single table

Anonymous — Tue, 10 May 2011 18:32:33 GMT

I did something similar to this earlier.

http://community.qlik.com/thread/27721?tstart=0

My problem that I am still having is the expression If(x = a or b or c, 1, 0) is returning true on every record. True or not.

Creating flags from multiple tables into a single table

Thu, 12 May 2011 14:06:59 GMT

Hi everyone thanks for your help with this.

How do I link another table with a smilar structure, but this time with up to 7 values in the colums i.e.

Key DrugsSeq Value

A 1 MOR

A 2 NOO

A 3

A 4

A 5 OXG

A 6

A 7

B 1 NOO

B 2 OXG

B 3

B 4

B 5

etc

What I need to do is look for where a distinct key has a value against it in any of the drugseq fields, which could be (MOR or NOO) and ASP and GTN. I used teh example above to map out each of the observation fields to a single line and this is straight foward because the observation field for a BP can be ISNUM. With the drugs it could be any value, on any line for that record.

I am struggling to link the table correctly without expanded it into 7gb RAM on just 100,000 records.

Any guidance would be appreciated.