Creating flags from multiple tables into a single table

[#1]

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

[nagaiank · Accepted solution]

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

1. There is a table of Keys:

Key

A

B

C

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.

[Anonymous]

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.

[#4]

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.