Skip to main content
O
OS_Pizza
March 30, 2023
Solved

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

  • March 30, 2023
  • 3 replies
  • 4 views

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 ?

 

Best answer by DanielWillis

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

3 replies

C
ChrisLoran
March 30, 2023

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

O
OS_PizzaAuthor
March 30, 2023

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

C
ChrisLoran
March 30, 2023

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" ?

O
OS_PizzaAuthor
March 30, 2023

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

 

D
DanielWillisAnswer
Employee
April 3, 2023

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

O
OS_PizzaAuthor
April 4, 2023

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

 

 

 

D
DanielWillis
Employee
April 4, 2023

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.

Terms & ConditionsAccessibility statement