Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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