ETL and Data Integration tools - Modern ETL software for everyone

Hi,

I have a transformation where I am utilizing a Calculation Transformation Function. This works fine until a record contains the ‘ character. The calculation then returns a null value as specified by the Calculation Properties for records in error.

How can I keep the ‘ character as part of my data field and not cause an error within my calculation? Sample from my data file is: 6 XXXXX's & 1 NSR100 Recorders

I thought about utilizing the AnsiQuotedStr function or the AnsiExtractQuotedStr function, but I am not sure how to use these and where to put them.

Please advise as to how I can make this work.

Thanks for your assistance!

Steve

Hi Steve

Two possible solutions
1 If there is only ‘ character and never two or more
use replace transformation function before calculation to replace ‘ with ‘‘ (two quotes not double quote)
after this calculation should work

2 use two replace transformation function one before one after calculation
use first one to convert ‘ to some string for example 55xx55
use second one to conver 55xx55 back to ‘

Mike,
DB Software Laboratory

Hi Mike,

Both solutions work!

Thanks for your assistance. Your response time is awesome!!
Steve