Formula Problem

[chc10]

Hi,

I'm having a problem with this formula:

IF Contains([CATEGORY], "CAT")
THEN Replace([CATEGORY], "CAT", "")
ELSEIF Contains([CATEGORY], "MM")
THEN Replace([CATEGORY], "MM", "")
ELSE [CATEGORY]
ENDIF

When the Category column has data

BABY NEEDS CAT

the formula returns Null even though it should strip out "CAT" and return "BABY NEEDS."

Does anyone have any ideas?

[fmvizcaino]

Hi @chc10 ,

I've just tested here and it is working as it should. Maybe your data type is set to a numeric one and it is returning null due to that.

Best,

FErnando Vizcaino

[chc10]

I agree formula should work but it's not.

Data type is set to V_String Size 1000.

[fmvizcaino]

Hi,

Would you be able to share your workflow and a sample dataset? Or some prints of your dataset entering the formula and exiting as well.

Best,

Fernando V.

[chc10]

When I use my output file to create an input file to send to you, the formula works.  I wonder if a solution is to run my workflow again, using output file as input?

Thanks

[grazitti_sapna · Accepted solution]

Hi @chc10 , rather sending the data directly to an output tool try using browse tool to see if you are getting the required output. Also if you will click on the output of formula tool it should work properly.

Other option is that to check before outputing the result using a select tool if the datatype of the field is being changed to double or int or any other datatype other than string?

Another possibility could be your data has a null in category field and that must be the first record, when you try to preview the output of the formula tool in itself it will display the formula for the very first row. Please refer to the screenshot.

But in actual it will work on whole column.

Thanks.

[chc10]

Hi,

I worked it out.

My formula works fine.

I had additional transformations after that step which caused this issue.

All good!

Thanks,