List input in sql query in Alteryx

[Sam_Rathi37]

 In my Alteryx workflow I've to input a list of client IDs which changes every month.

While I input the list from an excel file and run the SQL code in Input Tool connected to SQL server

I need to replace text SDS_IDs with the clients list in code "AND src_sys_key_idr in (SDS_IDs)".

I am trying to use Text Box and Action tool for this.

I am beginner in Alteryx. Anyone please help how I can achieve this.

[shreyanshrathod]

@Sam_Rathi37 ,

what is the structure of the list that you are importing from Excel?

Are all ID's present in a single record separated by comma OR all ID's are present in separate records ?

It will be helpful if you can attach a dummy list of your ID's and in which tool you are modifying the query.

Regards,

Shreyansh Rathod

[Sam_Rathi37]

All the Id's are present in separate records but I had them into single record using "Summary" tool to concatenate so each ID is separated by comma except for last one. 

I've the Sql query in Input tool where I'm using Text Box to edit date every month. How can I connect that Summary tool to replace the SDS_IDs in "AND src_sys_key_idr in (SDS_IDs)" in my sql code?

Dummy list: 11223311,22334411,54335654,.....

[shreyanshrathod]

@Sam_Rathi37 ,

I understand your query. Trust me, every single Alteryx user has faced this problem.

Unfortunately, Alteryx Designer does not support concept of variables / parameters to make things dynamic.

In your case, you want to make the list of ID's dynamic. To achieve this indirectly we have a workaround.

The LIST BOX tool in Interface Tool palette can be used here. 

Step 1:- Build a workflow that simply loads your LIST of ID's into another file (it can be an excel, a csv or even a .yxdb).

Note, the LIST BOX tool accepts input from a file and the column names should be 'Name' and 'Value'

Refer my LIST INPUT 1.yxmd workflow for the same.

Step 2:- In your main workflow, add the LIST BOX tool, configure it exactly as above screenshot and connect it to your input tool where you want to modify your query (In my example, I have connected it to a Filter tool).

The above configuration of LIST BOX tool will result into 'ID1','ID2','ID3, and so on.

In the ACTION tool, configure it to 'Replace a Specific string' into your INput tool.

Save your main workflow as .yxmc file.

Refer my attached MULTIPLE LIST INPUT.yxmc file.

Step 3 :- Now in a new workflow, insert this 'MUltiple List input' macro and save the workflow as .yxmd' Your UI will look something like this :-

Your TEXT BOX UI will also appear here. As of now, I have not included Text box, hence it doesn't show.

Step 4 :- The reason we build a separate LIST INPUT 1.yxmd in Step 1 is to populate the LIST BOX values dynamically at runtime.

SO the idea is, first this worflow will populate all the ID into the excel file as NAME and Value columns. The LIST BOX will then display all these values in the UI. You can select / deselect the values and run the workflow.

Note, if you wish to automate the running of both workflows, you can read here :-

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-to-run-a-workflow-from-an-event...

I have tested your query at my end and it works fine. 

Let me know if it helps.

Regards,

Shreyansh Rathod

[Sam_Rathi37]

Thanks Shreyansh.

I have replicated my workflow exactly as you mentioned below. But I am getting an error " SQL Macro (1) Tool #1: Error SQLPrepare: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between ',' and ';' "

To not have any inverted commas for the IDs I kept start text and end text blank.

And I connected the action tool directly with input tool which has the sql code.

Am I missing something? Please let me know what I should do.

Thanks and regards,

Sam Rathi

 

[shreyanshrathod]

@Sam_Rathi37 ,

Do not delete the START and END text single quotes. They are of extreme importance.

And the separator will also be ','

Also, I haven't used a semi-colon anywhere in my query. And your error message states that the error is between ',' and ';'.

I believe it's a very minute error somewhere in the syntax of your query. Kindly recheck.

Regards,

Shreyansh Rathod