Replacing Values

Hello,

In the workflow below, I am trying to replace values in the ETR and CashETR fields as follows:

- values below 0 should be reset to 0

- values above 1 should be reset to 1

I've tried using IF/ELSEIF and IIF formulas, but they don't seem to be working... I'm not quite sure how to make it work with FindReplace, it doesn't seem like I can set the parameters I need with that tool.

I think the data type may be the issue for ETR and CashETR, but if it changes, I need these fields to keep the same number of decimals for future calculations that need to be made with the Summarize tool.

The values in the left side of the join are the ones to be replaced.

Any help would be appreciated, thanks! Alteryx Replace.PNG

Alteryx Project 1.yxmd

Alteryx Data.xlsx

Workflow

Datasets

Parse

Help

Join

Data Investigation

Accepted answers

All comments

Tony_Castillo

FindReplace can only be used on strings. You'll need to change the data type of those columns to strings if you want to use that tool.

If you want to use a conditional formula (If/Elseif), then you may be running in to issues related to data types. You might have to use TOSTRING or TONUMBER depending on the route you go

Tony_Castillo

Changed the data type of the two columns from text to double. This allows the Conditional formula to work as attached.

Alteryx Project 1.yxzp

afv2688

Hello @AlexAlbro12 ,

Are you looking for something like this?

Regards

Alteryx Project 1.yxmd

AlexAlbro12

I need the values in the left side of the Join tool to be replaced, not the Join part.

In the previous step I had to filter out any records that had null values for PROA, LnAT, ForeignSales, Leverage, PP&E, R&D, NOL, DeltaNOL, Haven, ETR, and CashETR.

Whenever I moved the connection to Left origin, it gave me an error, see below.

I think the idea is correct, but not on the part of the Join I need.

AlexAlbro12

This did not fully work - I need the values in the left side of the Join tool to be replaced, not the Join part.

In the previous step I had to filter out any records that had null values for PROA, LnAT, ForeignSales, Leverage, PP&E, R&D, NOL, DeltaNOL, Haven, ETR, and CashETR.

Whenever I moved the connection to Left origin, it gave me an error, see below.

I think the idea is correct, but not on the part of the Join I need.

FlorianC

Hi @AlexAlbro12,

The error is caused by the fact that you are using numerical comparison operators on fields that contain text values. To resolve this, you must change the data types of the columns ETR and CashETR and then apply the formula.

Please see the Alteryx workflow package attached for a completed solution.

Example workflow

Alteryx Project 1.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703