Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
mikaelbe
Contributor
Contributor

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

View solution in original post

multiview
Creator II
Creator II

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

mikaelbe
Contributor
Contributor
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.

multiview
Creator II
Creator II

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