How to work with two tables at the same time

[#1]

Hi,

I have to test different fields on my fact table but i want to execute those test on two different rows.

So, i duplicate my fact table.

But now i don't understand how to make my test:

I want to test this(like a sql sequence):

SELECT a.Date_Out, a.IEMI, a.Symptoms, b.IEMI, b.Symptoms, b.Date_In

FROM a, b

WHERE (a.IEMI=b.IEMI AND a.Symptoms=b.Symptoms AND a.Date_Out <b.Date_IN AND b.Date_In <=a.Date_Out+30);

I tried with inner join but it dosen't work because when i want to test with table's b fields he doesn't find the fields of table a for example.

Any solutions?

Thanks in advance.

Regards

[brenner_martina]

Hi,

QlikView works allways only with one table, that's why you have to load both tables seperatly but you can join or concatente them, all fields with the same name will be keyfields and join the tables. You have to enter only one field as keyfield! If there are more then one field, concat them like:

Field1 &' '& Field2 As Keyfield:

Data:
Load Keyfield,Field1,Filed2,.... From Table1;

Concatenate (Data)
Load Keyfield,Field21,Filed22,.... From Table2;

OR

Data:
Load Keyfield,Field1,Filed2,.... From Table1;

Left Join (Data)
Load Keyfield,Field21,Filed22,.... From Table2;

The result is 1 Table with all fields and all data.

[#3]

Thank you for your prompt answer.

It works perfectly but now i have to make my test on two different rows so i think i have to use a pivot table right? Because QlikView can't work with two rows at the same time?

But i don't know how to use pivot table, do you know some tutorial to use it please or can you give me some explanations?

Thanks in advance.

Regards,

[pat_agen]

Hi,

follow Martina's advice (option 2 with the join command). look at the following

IEMI:
load *
inline
[
IEMI,Symptoms,date_Out
IEMIxyz,Sym123,20110101
IEMIxyz,Sym124,20110201
IEMIxyz,Sym456,20110301
IEMIabc,Sym457,20110101
IEMIabc,Sym123,20110201
IEMIabc,Sym124,20110301
IEMefg,Sym123,20110302
IEMefg,Sym124,20110303
];

IEMI:
inner join (IEMI) load *
inline
[
IEMI,Symptoms,date_in
IEMefg,Sym456,20110302
IEMefg,Sym457,20110303
IEMIxyz,Sym123,20110301
IEMIxyz,Sym124,20110201
IEMIxyz,Sym456,20110101
IEMIabc,Sym457,20110301
IEMIabc,Sym123,20110201
IEMIabc,Sym124,20110101
];

The result will be a table called IEMI with 4 fields IEMI, Symptom, date_out, date_in and only six records along the inner join of IEMI, Symptom. Now you you can do your tests on the date fields. Either carry on doing this in the script or dynamically in charts etc.

retrieving from sql the code will look like this - might be slightly different depending on which databse your running your query against, with Oracle all field names are sent back in upper case, but the syntaxe should work:

IEMI:
load IEMI,
Symptoms,
date_Out;
sql select
IEMI,Symptoms,date_Out
from a;

IEMI:
inner join (IEMI) load
IEMI,
Symptoms,
date_in;
sql select
IEMI,Symptoms,date_in
from a;

hope this helps.

[#5]

Hi,

I understand what do you mean, and i see that i've made a mistake so my join results to nothing...

It works fine now.

Thanks a lot for your help both of you!

It was the occasion to learn more about Qlikview's logic and to see that i still have a lot to learn!

Regards,

Renaud