Replacing Values

[AlexAlbro12]

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!

[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. 

[afv2688]

Hello @AlexAlbro12 ,

Are you looking for something like this?

Regards

[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.

[AlexAlbro12]

The error is Error at char (31): Invalid type in operator >. (Expression #1)

[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