Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Analytic App - Transform text box input value

nsmith293
7 - Meteor

Hello,

 

I'm new to a lot of this, so I'm sure I've missed this simple answer but....

 

I have a text box input for an app.  The user will enter comma-separated values like this:

 

apple,pear,orange

 

This input will pass as a parameter to a sql statement, so I need it to be passed as:

 

'APPLE','PEAR','ORANGE'

 

I know this function will do the trick as far as changing the value, but I don't see how to pass it as the parameter using a "replace string" approach in the Action tool:

 

"'"+replace(uppercase([text_box_value]),",","','")+"'"

 

Any advice on how to transform/massage a text box input being passed a sql statement parameter would be greatly appreciated.

 

Thanks!

5 REPLIES 5
nsmith293
7 - Meteor

Soon after posting this I thought of a work-around (or maybe it's the intended approach).

 

My original approach was trying this:

 

Text Box--> Action --> Input Oracle OCI Sql --> Output to Excel

 

The solution that worked was:

 

Text Box --> Action --> Input Text --> Formula --> Dynamic Input --> Output to Excel

 

Still seems like I should be able to pass a transformed input value as a parameter into my sql statement using my first approach, but the second approach does appear to work in a straightforward manner.

 

Thanks.

SeanAdams
17 - Castor
17 - Castor

:-) you should give yourself credit for the solution! (and mark this as solved)

 

 

Have a good week

Sean

patrick_digan
17 - Castor
17 - Castor

@nsmith293 I think you could do something like this if you wanted to stick with an action tool (update with formula) and an input tool setup:

Replace([Destination],"TEXT","'"+replace(uppercase([#1]),",","','")+"'")

You would just need to add another replace where you read in the destination and replace the text (I called it TEXT). My input tool SQL looked like this:

Select * From TABLE where Field IN (TEXT)

The action tool is the 1 tool that I know of that can use the data from both sides to build a formula. 

 

nsmith293
7 - Meteor

@Patrick_digan Thanks I'm still learning how the action tool works, and I see your solution would do the trick.  I think the trade-off is maybe the comfort with doing complex formulas in the action formula vs just using a generic approach that also supports null checks and then passing that to a text input and then a formula tool to do the comma-separated list.  The latter gives more flexibility to evaluate/modify/etc. the input values as opposed to having to do it all in one action formula.

 

So, in my case, I need to do what I mentioned about formatting the comma-separated list, but I also need to manage nulls.  So use this in my action formula to handle nulls:

 

if isempty([#1]) then replace([Destination],"[text input field value]","nullentry") else [#1] endif

 

This lets me control null/optional fields as well in my approach. I think use logic to transform "Nullentry" appropriately in my replace string for the sql statement.

 

Overall, it's good to see the flexibility, and I'm learning to think differently in how to flow this out in Alteryx vs a mindset using Oracle pl/sql, etc.

 

Thanks!

emkiely
5 - Atom

I am trying to do the same thing except my data field in my sql is an integer.

An associate gave me this to use but it is not working:

replace([Destination], "And Load_id In (0) ", "And Load_id In ("+REGEX_Replace(([#1]), '\n', ",") + ")")

I get results, but just for load_id 0.

 

I am replacing 0 in my sql with a list of Load id's that the user enters.

Load_id is an integer in the source data.

 

Here is my sql 

SELECT "LOAD_DIM"."LOAD_ID","LOAD_DIM"."FRT_MOVE_CATG_CODE","EDI_MSG_TRACKING_FACT"."MSG_EVENT_TS","EDI_MSG_REASON_DIM"."MSG_REASON_CODE"
FROM "US_TRANS_DM_VM"."LOAD_DIM","US_TRANS_DM_VM"."EDI_MSG_TRACKING_FACT","US_TRANS_DM_VM"."EDI_MSG_TYPE_DIM","US_TRANS_DM_VM"."EDI_MSG_REASON_DIM"
WHERE current_ind='Y'
AND "LOAD_DIM"."LOAD_SK_ID" = "EDI_MSG_TRACKING_FACT"."LOAD_SK_ID"
AND "EDI_MSG_TRACKING_FACT"."MSG_TYPE_SK_ID"="EDI_MSG_TYPE_DIM"."MSG_TYPE_SK_ID"
AND "EDI_MSG_TRACKING_FACT"."MSG_REASON_CODE_SK_ID" = "EDI_MSG_REASON_DIM"."MSG_REASON_CODE_SK_ID"
AND "EDI_MSG_REASON_DIM"."MSG_REASON_CODE" IN ('AF','AI','AJ','AY','A1','AM','AG','AO','AV','BB','AL')
And Load_id In (0)

Labels