Replace dynamic input with codes from another file for each row cobmbination

Question

Im connected to a data source and have piece in my query where the columns CHANNEL_CODE and CATEGORY_CODE need to equal the codes that are in the same rows as the Text File Input. I also need a sample of 10 records from each row combination.

Objective: Run and replace the codes for each row that has both codes in the CHANNEL_CODE and CATEGORY_CODE columns and return 10 records from each instance.

Canvas Screenshot.png

Source Data Filtering Example.png

Text File Input.png

davidskaife · Answer

Hi @alexisaharrison

I've pulled together this example, which you can hopefully unpick and use for your specific case (as i cannot build it on a database connection so have had to improvise). I've also done this quite quickly so apologies for any errors!

You can achieve what you're after by building your SQL query using a Formula tool, and replacing the codes with the data held in the Text Input:

This will generate an SQL statement for each row in the Text Input.

Next feed this into a Macro which will run the SQL command in the Input Data tool within. I've used a Text Input as the example but you should be able to use this as a guide

Finally the Top 10 clause of the SQL limits it to the first 10 records, this syntax may not be exact for the database you're using but Limit generally works as well - you may have to search to identify what the correct syntax is.

Another option is along the same lines but using the Dynamic Input tool - more in depth details here https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Modifying-SQL-Query-using-the-Dynamic-Input-Tool/ta-p/17431

Dynamic SQL.yxzp