topic Need to join two tables like in sql in QlikView

Need to join two tables like in sql

Wed, 30 Dec 2009 18:50:10 GMT

Hi,

Is it possible like in sql ,can we load one table based on condition (field from another table comparison to this table field)

example:

table1::

load f1,f2 from ...qvd;

table2::

load g1,g2 from ...qvd;

table3:

load f1,f2 from table1 where table1.f1=left(table2.g2,2) ;

Need to join two tables like in sql

Wed, 30 Dec 2009 19:05:46 GMT

Hi,

You can do it but not as in SQL, there are other methods of doing this e.g using joins. Please see an example below:


table1:
load * inline [
f1,f2
AP,10
JP,20
];
test:
load * inline [
g1,g2
APAC,10
];
table2:
load
*,
left(g1,2) as g3
resident test;
drop table test;
table3:
load
g3 as f1
resident table2;
inner join (table3)
load f1,
f2
resident table1;
drop table table1;
drop table table2;

Need to join two tables like in sql

justinasp — Wed, 30 Dec 2009 19:36:30 GMT

Hi, Blessy

I think You must have the same values as a key to compare two different tables (in Your case left(g2,2) should be a value).

This way the solution could be:


table1:
load * inline
[f1, f2
a1, 2
b1, 3
c1, 1
];
table2.temp:
load * inline
[g1, g2
d1, a1aa
d2, b1aa
d3, d1aa
];
table2:
load g1,
 g2,
 left(g2,2) as g3
resident table2.temp;
drop table table2.temp;
table3:
load f1 as t1,
 f2 as t2
resident table1
where exists(g3, f1);