03-30-2023 05:34 AM - edited 03-30-2023 07:20 AM
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 ?
Solved! Go to Solution.
04-03-2023 07:15 PM
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
03-30-2023 08:02 AM
Or use SQL REPLACE function , REPLACE(MyColumn,'_0','')
03-30-2023 08:04 AM
Using functions in Select statement makes the data disappear in the import step 😞
03-30-2023 08:27 AM
Here's an example of using REPLACE in a SELECT statment where data does not disappear:
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" ?
03-30-2023 08:41 AM
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.
04-03-2023 07:15 PM
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
04-04-2023 02:31 AM
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?
04-04-2023 06:29 PM
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.