Use the results from the previous query to query from different database

We have a orders table and a contacts table. The contacts table has close to a billion records. These two tables are in two different DBs

I need to select orders based on a criteria. Then for each order, I need to lookup the contact record(s) by contact_id. There is a many-to-many relationship between the two tables

One option is to select the orders by criteria and then pull ALL the data from the contacts table, and then perform a join using the join Join Shape. This works, but takes forever, especially if the contacts data is not cached. We can not cache the contacts data as it being constantly updated.

We only select ~1000 orders at a time, so using the Join shape create a huge overhead.

Is there a better way to implement this?

Join

Database Connection

Accepted answers

LordNeilLord

I've been meaning to set this up for a while, so I thought I'd do it now for you

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Data Lover

DynamicInput.yxzp

All comments

LordNeilLord

Hi @saqib

I come across this problem quite a lot and here is my solution to that problem:

Start with a standard input tool to pull back all of the orders you need...
Use a summarize tool to concatenate all of the contact_ids into a single field (comma separated)
Use a dynamic input tool to update the query from your contacts table (you can update the where clause to say... Where contact_Id in ([your concatenated contact_ids])

This is basically the same as doing a join from orders to contacts but your are heavily restricting the results from contacts.

See if you can get that working, if not let me know and I'll send you an example

Neil

LordNeilLord

I've been meaning to set this up for a while, so I thought I'd do it now for you

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Data Lover

DynamicInput.yxzp

saqib

Hello,

Thanks for the suggestion. This is an awesome solution.

This works, but only when the WHERE IN clause is only few hundred contact ID. Beyond that Alteryx throws a Unhandled Exception. Now sure why.....

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703