Remove suffix from source data where data is coming from a table.

OS_Pizza
Contributor III

Source

A10000_0

Target

A10000

How can we achieve it in different ways?

1. Connector SQL query -> Using this in the sql query Select left(Account, len(Account)-2) leaves blank rows in the import step.

2. Data Source ?

     2.a. Logical expression

             Return args.Value.Replace("_0", String.Empty) - This works fine.

     2.b. What about Text Fill settings or Substitution settings?

3. Transformation Rule ?

 

1 ACCEPTED SOLUTION

It shouldn't. Is it something like you're not naming your column when using the function? E.g., should perhaps be: Select left(Account, len(Account)-2) AS Account

View solution in original post

7 REPLIES 7

ChrisLoran
Valued Contributor

Or use SQL REPLACE function  ,   REPLACE(MyColumn,'_0','')

Using functions in Select statement makes the data disappear in the import step 😞

ChrisLoran
Valued Contributor

Here's an example of using REPLACE in a SELECT statment where data does not disappear:

ChrisLoran_0-1680179069803.png

I am pretty sure this result set would appear in the import step just as it appears in the query result list.
Could you be more specific on what is meant by "the data disappears" ?

It works fine in the SQL query but when you use it in the connector rule , the data doesnt show up in the RegisterID column.

OS_Pizza_0-1680180098141.png

 

It shouldn't. Is it something like you're not naming your column when using the function? E.g., should perhaps be: Select left(Account, len(Account)-2) AS Account

OS_Pizza
Contributor III

Absolutely ! I have missed that , Thanks @DanielWillis @ChrisLoran . This will save a lot of time by discarding complex expression used at Source Dimension. Better to clean data at source then at mapping.

One more question. Do you happen to know about -

1. Text Fill settings or Substitution settings. What is the purpose of these two?

OS_Pizza_0-1680589855727.png

 

 

 

Hi Pizza. It's generally accepted best practice to mark a topic as solved and ask any new questions in a new thread to ensure others can benefit from your experiences.