Formula tool - IF condition output mismatch

[Inactive User]

Hello,

I have applied IF condition from column B to F based on column A but my output isn't accurate.

below is the expected output

Stock Percentage >= 20% && <=39.99 THEN 1 ELSE 0
Stock Percentage >= 40% && <=59.99 THEN 1 ELSE 0
Stock Percentage >= 60% && <=79.99 THEN 1 ELSE 0
Stock Percentage >= 80% && <=99.98 THEN 1 ELSE 0
Stock Percentage >= 99.99% THEN 1 ELSE 0

Please find the attached table data and advise.

[BrandonB · Accepted solution]

This is because your stock percentages are numbers. Instead of >= 20%, try doing >= .2

Also, you will need to use the column name for the second criteria

IF [IR Stock Percentage] >= .2 AND [IR Stock Percentage] <=.3999

THEN 1

ELSE 0

ENDIF

[atcodedog05 · Accepted solution]

Hi @Inactive User 

Here is a working workflow on the usecase.

Workflow:

Instead of taking <=39.99 I am using <40 which is the same logic.

Hope this helps : )

[apathetichell]

your data isn't great. You have 2,600 rows - but with 271 unique identifiers of which 260 are names. There is now secondary primary key, so you basically have 260 values - but 2600 entries for those values... The bulk of your entries don't fit your framework, and for some reason I had some serious regex issues on your column names and getting the percentages out...

just an fyi 20% isn't a number .2 is - that's why you can't do a compare like that... see attached for a potential solution.

[Inactive User]

Thanks it's working but i'm unable to convert string 0.2 to 20%. How do i fix this?

[atcodedog05]

Hi @Inactive User 

Percentage is not a supported datatype in Alteryx. 20% will be treated as string.

You can do something like below to show it in percentage format.

Beware: this will be treated as string format in excel too.

Hope this helps : )

[Inactive User]

I'm receiving below error

[atcodedog05]

Hi @Inactive User 

Try something like this.

ToString(ToNumber([Number])*100)+"%"

It seems like the column was not numeric that's why above also I used select tool to convert it to numeric.

Hope this helps : )

[Inactive User]

Still same issue, i have attached the table for your reference and column A should be converted to 0.1864 to 18.64%

[atcodedog05 · Accepted solution]

Hi @Inactive User 

I have modified the above workflow.

Workflow:

Hope this helps : )