topic Connecting to two different data sources and limiting data in tables. in QlikView

Connecting to two different data sources and limiting data in tables.

Wed, 29 Sep 2010 11:41:01 GMT

Hi,

I have two database sources.

connect(Datasource1)

Load A,B from table_1;

connect(Datasource2)

Load A,C from table_2;

Now, table_2(from Datasource2) contains redundant data and hence I would like to limit it to the data that is present only in table_1(from Datasource1)

e.g

In table_1

value of A = {1,2,3}

In table_2

value of A = {1,2,3,4,5}

but In table_2 I would like to load values which corresponds to A={1,2,3}

Can anyone help me with this?

Please let me know if you need further information.

Regards,

Rohit

Connecting to two different data sources and limiting data in tables.

jonathandienst — Wed, 29 Sep 2010 12:16:56 GMT

You can use a LEFT JOIN to get that:


connect(Datasource1);
DataTable:
Load A,B from table_1;
connect(Datasource2)
Left Join (DataTable)
Load A,C from table_2;

Jonathan

Connecting to two different data sources and limiting data in tables.

Wed, 29 Sep 2010 12:22:36 GMT

Thanks!!

That solves my problem 🙂

Connecting to two different data sources and limiting data in tables.

Wed, 29 Sep 2010 13:14:47 GMT

Hi,

Although now it shows the correct data but still the complete data is beeing loaded from table_2.

table_2 contains lot of redundant data and also takes a lot of time to load all the data.

My ultimate aim is to limit the data that is beeing loaded.

Is that possible?

Connecting to two different data sources and limiting data in tables.

Miguel_Angel_Baeyens — Wed, 29 Sep 2010 13:23:13 GMT

Hello,

Further to Jonathan's example, use the EXISTS() function in WHERE statement

ODBC CONNECT TO Database1;Table1:LOAD A, B;SQL SELECT A, B, FROM Database2.Table1; ODBC CONNECT TO Database2;Table2:LOAD A, CWHERE EXISTS(A);SQL SELECT A, CFROM Database2.Table2;

Hope that helps

Connecting to two different data sources and limiting data in tables.

jonathandienst — Wed, 29 Sep 2010 13:37:43 GMT

Rohita

Both my Join and Miguel's Exists work in QV, so all the data must first be fetched from the DB.

You can limit the data being fetched by doing the join in the SQL query. This will only fetch the matching data (and does it in a single fetch):


connect(Datasource1);
Load T1.A, T1.B, T2.C from table_1 As T1
LEFT JOIN table_2 As T2 ON T1.A = T2.A;

(The syntax will depend on the type of DB you are using. This should work on most)

Jonathan