Formula in an Action tool

I am using an action tool to update a value in a dynamic input sql editor and I'm using IIF for the formula. What i want to accomplish is to have the user use a check box to update the sql code. if it is unchecked i want that string in the sql code to be deleted since Alteryx doesn't like commented out lines.

Is there a solution to this?

I've tried both of these formulas:

IIF([#1] = 'TRUE', Replace([Destination], '--and PAYMT_RATING =', 'and PAYMT_RATING ='),Replace([Destination], '--and PAYMT_RATING =', ' '))

IIF([#1] = 'TRUE', Replace([Destination], '--and PAYMT_RATING =', 'and PAYMT_RATING ='),Replace([Destination], '--and PAYMT_RATING =', ''))

This is the line in the SQL editor:

--and PAYMT_RATING = 'Rating'

The "'Rating'" is being updated by a different Text Box -> Action -> text input tool.

Workflow

Accepted answers

All comments

Aguisande

Hi @jordanmount6

It should work (theoretically). Without seeing the actual macro/WF I can't determine how [#1] is received by the Action tool.

Can you try using this expression (Since [#1] is a boolean) :

IIF([#1] , Replace([Destination], '--and PAYMT_RATING =', 'and PAYMT_RATING ='),Replace([Destination], '--and PAYMT_RATING =', ' '))

And let me know

jordanmount6

I am still getting the same error: Dynamic Input (2): DataWrapOCI: Unable to prepare the query .

I am assuming it just has to do with the lines in the editor that i am commenting out but they should all be getting uncommented out by these action tool formulas.

jordanmount6

I should also note that within the Query that this is updating there is a Union all statement, if that matters.

TheOC

hey @jordanmount6

Can you please upload a snippet of your workflow where you are trying to do this, with some example data? Feel free to remove anything identifiable or change the data.

Cheers,
TheOC

Aguisande

Can you share more specifics on your actual workflow?

Maybe to catch how the query is before entering the Dynamic Input Tool? And how the Action Tool is affecting the WF?

Or maybe a Zoom to see it and try to help you

jordanmount6

The query that i am trying to update with the formula in the action tool is something like this:

where date between 'Start' and 'End'
--and status = 'Status'
--and x = 'x'
--and code = 'code'
--and y = 'y'
--and s = 's'
--and PAYMT_RATING = 'Rating'

The action tool is specifically uncommenting whichever line the user will check on the interface when they run the app. The text input is updating and then the dynamic input is replacing string in whichever line is uncommented.

Hopefully that makes sense.

jordanmount6

The query itself works fine in another platform.

jordanmount6

I have removed the input connections and the output connection because of sensitive info. everything else remains the same. Here are the queries in the inputs:

select
ACCT_STATUS,
AMT_PAST_DUE,
CBR_ACCT_NUMBER,
CBR_CMNT_CODE,
CHARGE_OFF_BALANCE,
CREDIT_LIMIT,
CURR_BAL,
DATE_CLOSED,
DATE_LAST_PAY,
DATE_OPENED,
BOL_RELATION_CD,
MTGR_CONSUM_INFO,
PAYMT_HISTORY,
PAYMT_RATING,
RUN_YR_MO,
LN_COMPLIANCE_CD
FROM y
where run_yr_mo between 'Start' and 'End'
--and ACCT_STATUS = 'Status'
--and MTGR_CONSUM_INFO = 'CII'
--and CBR_CMNT_CODE = 'SCC'
--and BOL_RELATION_CD = 'ECOA'
--and LN_COMPLIANCE_CD = 'CCC'
--and PAYMT_RATING = 'Rating'

union all

select
ACCT_STATUS,
AMT_PAST_DUE,
CBR_ACCT_NUMBER,
CBR_CMNT_CODE,
CHARGE_OFF_BALANCE,
CREDIT_LIMIT,
CURR_BAL,
DATE_CLOSED,
DATE_LAST_PAY,
DATE_OPENED,
BOL_RELATION_CD,
MTGR_CONSUM_INFO,
PAYMT_HISTORY,
PAYMT_RATING,
RUN_YR_MO,
LN_COMPLIANCE_CD
FROM x
where run_yr_mo between 'Start' and 'End'
--and ACCT_STATUS = 'Status'
--and MTGR_CONSUM_INFO = 'CII'
--and CBR_CMNT_CODE = 'SCC'
--and BOL_RELATION_CD = 'ECOA'
--and LN_COMPLIANCE_CD = 'CCC'
--and PAYMT_RATING = 'Rating'

The bottom inputs query:

Select
cnsmr_acct_nb,
cr_rpt_furn_dt,
cnsmr_acct_opn_dt,
cnsmr_acct_cr_lmt_am,
cnsmr_acct_sts_cd,
cnsmr_pymt_rt_cd,
cnsmr_pymt_hist_desc_tx,
cra_spcl_cmnt_cd,
cnsmr_curr_bal_am,
cnsmr_past_due_am,
cnsmr_chrgf_am,
cnsmr_acct_clse_dt,
cnsmr_last_pymt_dt
cnsmr_ecoa_cd,
cnsmr_info_cd,
cnsmr_cmpl_cnd_cd
FROM w
Where cr_rpt_furn_dt between 'Start' and 'End'
--and cnsmr_acct_sts_cd = 'Status'
--and cra_spcl_cmnt_cd = 'SCC'
--and cnsmr_ecoa_cd = 'ECOA'
--and cnsmr_info_cd = 'CII'
--and cnsmr_cmpl_cnd_cd = 'CCC'
--and cnsmr_pymt_rt_cd = 'Rating'

Workflow App.yxwz

jordanmount6

any thoughts? @TheOC

TheOC

hey @jordanmount6

Sorry for delay getting back to you!

I thought the wf would help but its hard to troubleshoot in current:

What you can do is use the debug tools in Alteryx to investigate this. If you go to interface designer:

Go to the 2nd option:

And then fill out the details, if you hit 'open debug', this will create effectively the workflow that would run, with interface changes made.

This will let you run the workflow, and see the updated query/data at each stage. If theres any issues with the query, they should be easy to spot at this stage.

Hope this helps

TheOC

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703