Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Create query based on input data

Highlighted
8 - Asteroid

Hi guys 

Im in desperate need of a solution for this.

I have a input file containing:

Customer - Shipment id
Dennis        123456785
Martin         1111122257
Jens            222333564

I want to compare the shipment id to my mysql database

So I would love to be able to do something like 


SELECT * FROM 
Integration.booking_line bl

 

where

bl.shipment_awb  IN (

[Shipment_id]
)

is this in any way possible?

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @Hamder83 ,

 

it should be possible using the Dynamic Input tool. Create a query as you would do using the Input Data tool in "Input Data Source Template" including the "Where" statement. Modify SQL Statement provides the option to replace your template where clause by the specific list of Ids from your file. This list can be generated using the Summarize tool (concatenate entries). 

 

Let me know if it works for you or if you need more detail.

 

Best,

 

Roland

 

 

Highlighted
8 - Asteroid

Hi Robert 

I dont think i have the knowledge to do that..

May I ask for you to create a super simple flow just with a text input and the dynamic input joined witha  db connection?

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

No problem. I enclosed the IDs in quotes, this is only needed if the database field is numeric. But to use "concatenate" in the Summarize tool, the field has to be a string data type.

Highlighted
8 - Asteroid

Hi Roland 

Ive tried setting it up like this : 

ive added a DB (without a where clause?) 

Hamder83_0-1588155670960.png


But nothing happends when i run the flow ..

Do i need to add anything here?

 

Hamder83_1-1588155705417.png

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

The SQL is missing the template where clause. I tihkn, you should add something like "WHERE b.Id IN('999999')" to your SQL (if b.Id is the field the restriction should be applied on).

In the "Update SQL" section, click on EDIT. In the drop-down, you have to select your where condition, Text to Replace would be '999999' and the Replacement Field is the concatenated field you created before.

Highlighted
8 - Asteroid

Hi again Robert, 

It almost works.

I changed it as you suggested - though clicking (Group Replacement Value For SQL IN Clause) on.

Hamder83_0-1588159279269.png

The query runs perfectly, but i does not return anything.

And if i run this query in my sql editor, i get results:

 

select

b.consignee_number, bl.carrier_name, bl.shipment_AWB
from
Integration.booking b
inner join Integration.booking_line bl on b.Id = bl.booking_id

where
bl.shipment_AWB in (

'4477179818',
'4631330927',
'4631369529',
)


So, what am i doing wrong?

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @Hamder83 ,

 

double checked it with my database, it seems, concatenation and adding quotes are not needed, as the complete column is used for an "IN" statement. I've modified the sample, hopefully it works now as expected.

 

Best,

 

Roland

Highlighted
8 - Asteroid

Hi 

As always, your ideas work like a charm!  

I have no knowledge of this setup. But i tried running a sql getting all data from past 90 days (280.000 records) and i did it this way.

The first query finished in 3mins. This one has run for an hour without ending (only 80.000 records). Is this a slow way of doing it or am i missing something?

Labels