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

[saqib]

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?

[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 · Accepted solution]

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

[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.....

[LordNeilLord]

Hey @saqib

It throws an error because the summarize tool has a 2048 string limit.... To overcome this:

-Remove the last summarize tool
-add a formula tool with a name header and value "header"
-add a crosstab tool group by nothing set the header as header and values as your customer_Id... Choose the option as concatenate... Comma separated and set the field length to 9999999

And that's it :)

Let me know if you can't get it to work and I'll mock you up another demo

Neil

[saqib]

Still hitting the field size limit for the concat on the crosstab shape. Looks like the max field size for that is 2000000000

[LordNeilLord]

Ahhh OK you must be pumping lots of values into it!

You might want to split the values up into batches (for example 100 at a time) the dynamic input will run through them and will automatically union all of the results together

Neil

[LordNeilLord]

@saqib

Here's what I was talking about

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[saqib]

Hi,

Yup, I underestimated the amount of data we are getting from the Orders table. So I have created a batch macro that is processing 1000 records at a time. :)

Saqib