Filter Tool

[Chirag_Gandhi07]

Hi,

Using the formula tool, I created a conditional statement for a new column, Hardware and PPT Asset Comparison: IF [Asset Rental Amount] != [Base Payment] THEN "0" ELSE "1" ENDIF. I then used the filter tool where Hardware and PPT Asset Comparison Equals 0. When I run the workflow, I am getting the one's that don't match, but I am also getting about 100 records that do match. They have the same data type (double) and are rounded the same. For example: 22.51. Is there any reason why they are being shown on the true part of the filter tool? Any answers would be truly appreciated.

Best,
Chirag Gandhi

[BenMoss]

Can you please share an example workflow and data file where this issue occurs.

Ben

[Chirag_Gandhi07]

Unfortunately I can't as this is sensitive information. Maybe it has to do with the spacing?

[ydmuley]

Hello @Chirag_Gandhi07 Its difficult to understand what exactly you are trying to achieve as I am unable to see the data.

However- You can check if the filter issue is because you are trying to convert ZERO (0) to string - "0".

You can try to leave it as is and see if it works. Also, if there is a decimal difference between two numbers try to limit the decimal limits first so that the values can match,.

[ydmuley]

Possible due to spacing, you can use Data Cleansing tool on those two columns to trim the leading and trailing spaces.

Post that you also change the data type to int64 or Fixed Decimal based on what type of values you are having.

[BenMoss]

I would follow the advice from @ydmuley but it wouldn't be to difficult to copy say just two cells of data out, one row, both numeric into a new file. Without the context we would have no idea what it's about!

Ben

[Chirag_Gandhi07]

I used the data cleansing tool and changed the data type to fixed decimal and int 64 but nothing worked. Still getting the same results. They both are rounded to two decimal places so they should match, but should I try changing the decimal places it rounds to for those specific columns. If so, how would I go about doing that?

[PaulN · Accepted solution]

Hi @Chirag_Gandhi07,

Thank you for your message. 

Based on your description, I suppose that you are in the case described there: https://help.alteryx.com/current/FAQ.htm, "I am using a simple filter:[Value] > 2. Yet I am getting values equal to 2 in my TRUE stream. Is thi...

Long story short: it has to do with the nature of doubles and how they are stored.

You could either try to convert the values to string before comparing them (ToString()) or use CompareDigits() to ensure that numbers are compared correctly.

Note that if you are not planning to use the value of asset comparison field, you could skip the formula tool.

Kind regards,

Paul Noirel

Sr Customer Support Engineer, Alteryx

[Chirag_Gandhi07]

The above file is just two records. One matches and the other doesn't but they both have a 0 for the Hardware and PPT Asset Comparison column. I only want 0 for the one's that don't match comparing Asset Rental Amount and Base Payment. Not sure what the problem is