How to load data from one database out of the result from another database?

[Anonymous]

Hi,

I have two databases one in mysql and one in postgresql. Each of these two databases has a table that has a column called "deviceId". The "deviceId" column in the postgresql database however contains a lot of more entries than the "deviceId" column in the mysql database.

I need a way to get only the rows from the table in the postgresql database that has a "deviceId" value that exists in the mysql database. How do I create a script that can do this?

BTW, the databases are huge so I can not load the whole table from postgresql into qlikview and afterwards sort out the unwanted data. I need to do this filtering in the sql query so I only will receive the result that I am interested in.

Thanks

[Gysbert_Wassenaar · Accepted solution]

I don't think you can do this directly with qlikview. Because the data is in two separate databases qlikview and you have to use sql selects to get the data qlikview needs to load all the data from both tables.

The obvious solution is to retrieve a distinct list of deviceid's from mysql and load these into a (temporary) table in the postgresql database. Then you can create an sql statement that only returns the records from the postgresql table that have an deviceid that also exists in the deviceid list from mysql.

[Anonymous]

I assume that your data is huge, i.e millions of rows. This is a situation where  you should utilize Qlikview's in-memory computing to get you the results in a fraction of seconds.

Gysbert's idea is the solution that works out of QlikView.

If you want to do it in QlikView then these are the steps :

1 - Load the table from PostgreSql to QlikView.

2 - Load the table from MySQL.

3 - Inner join between the two tables using Resident load.

In addition to QlikView's performance advantage, you could also verify and reconcile your data easily.

I hope this helps!

Regards

MultiView

[Anonymous]

Thanks to both Gysbert Wassenaar and Multi View for saving me a lot of time in researching how to do this just to find out that it is not possible. After all I ended up in using the method described my MultiView, eg. loading the whole tables from both databases into memory and then joining them. This was because the databases are not mine and I have no write access to them so I could not create a temporary table as suggested by Gysbert. It now takes me about 10 minutes to load all data but, what the heck, at least it works.

[Anonymous]

mikeaelbe thanks for your update. But I think you need to mark this as answered. The answer was its not possible to do it in Qlikview as mentioned by Gybert.

Thank you

MultiView