concatenate and link tables and my problem

[#1]

I expose an example,I think is better to can answer my question.

Suppose a Table of pharmacy

patient id  drug  date_pharmacy  center          service      assistance

1              A        20150101            1                  100          15H100

1              B        20150203            1                  201        15H320

2              A        20150210            1                  100          15H330

2              A        20150705              2                  500        15H403

2              C          20150705            2                    200          15H403

3              A          20140312            2                    100        14H342

4              B          20140123            1                    200        14H001

other of laboratory

patient id  center      service      assistance              test  date_lab

1              1              200        15H100                    3      20150101

1              3              423        15H100                    2      20150102

1              2            200        15H191                      1      20150124

3                1            100            14H342                  3      20150801

other Radiology

patient id  center      service      assistance              RX      date_rad

1              1              500        15H100                    123      20150101

6                3            100          15H111                  23        20150223

1                2             100         15H400                  123        20150227

if I concatenate.

patient id  drug  date_pharmacy  center          service      assistance            test            date_lab                  RX      date_rad

1              A        20150101            1                  100          15H100

1              B        20150203            1                  201        15H320

2              A        20150210            1                  100          15H330

2              A        20150705              2                  500        15H403

2              C          20150705            2                    200          15H403

3              A          20140312            2                    100        14H342

4              B          20140123            1                    200        14H001

1                                                      1                    200        15H100                  3          20150101

1                                                      3                    423      15H100                  2          20150102

1                                                      2                      200      15H191                1            20150124

3                                                      1                      100      14H342              3              20150801

1                                                      1                        500      15H100                                                      123                  20150101

6                                                      3                        100      15H111                                                        23                  20150223

1                                                      2                         100      15H400                                                        123                 20150227  

I have a lot of Fact tables,  and I need to can answer questions beetween fact tables, and I don't know if concatenation is the best way or is best link tables.

Can I answer, with concatenation,  the patients  of drug A , that they have a test type  3?

Can I answer,with concatenation,  the assitances of laboratory type=3 , that they have a Radiology test?

It's possible to make two or more link tables?.

link tables for:

patient id

medical assistance id

center, 

healthcare_service,

.

Thank you very much.

[marcus_sommer]

Unfortunately there isn't a best way - it will be depend on many factors which approach is suitable for you but this Concatenate vs Link Table and this Get started with developing qlik datamodels will be helpful for you.

- Marcus

[Gysbert_Wassenaar]

Since the tables have a lot of fields in common my first thought is to see if the tables can be concatenated. I would rename the date fields so you have only one date field and I'd add a source field to store where the data came from.

Can I answer, with concatenation,  the patients  of drug A , that they have a test type  3?

Yes. The easiest way is to first select drug A, then select the possible patient_id values. Next unselect drug A. Finally check if any selected patient id's have test type 3. If test type 3 is a possible value there are patients with drug A and test type 3.

It's also possible to create set analysis expressions that do this kind of filtering in an expression. Something like:

count({<drug={'A'},[patient id]=P({<drug=,[test type]={3}>}[patient id])>} [patient id])

Can I answer,with concatenation,  the assitances of laboratory type=3 , that they have a Radiology test?

Yes, using the same procedure as above.