Logic options

[devsonali]

Hello all,
I have a logic where in a string say
SET 1
STRING 1 = 'A SAMPLESTRING'
STRING 2 = 'ASMP A STRING"
STRING 3 = 'ASMP STRING A"

SET 2
STRING 1 = 'AB SAMPLESTRING'
STRING 2 = 'ASAMPL AB STRING"
STRING 3 = 'ABSAMPLE AB"

The rule is in a given string if it is either appears as "A " (A SPACE)
or " A " (Space A Space)
or " A" (Space A) Then it should be substituted by 1 so set 1 should become
STRING 1 = '1 SAMPLESTRING'
STRING 2 = 'ASMP 1 STRING"
STRING 3 = 'ASMP STRING 1"

Similarly ' AB ' or 'AB ' or ' AB') becomes 2
SET 2 becomes

STRING 1 = '2 SAMPLESTRING'
STRING 2 = 'ASAMPL 2 STRING"
STRING 3 = 'ABSAMPLE 2"

There are a few conversions of this kind and I can do it by using substring and if else statements , I was wondering if there is a better way to achieve this by having a look up table which has entries like

FROM_LIST TO_LIST
'A ' 1
' A ' 1
'AB ' 2
' AB ' 2

So on ..

[ray.wurlod]

Nested Ereplace() functions should do it. For example, and not including " A ":

Code: Select all

Ereplace(Ereplace(InLink.TheColumn, "A ", "1", 1, 1), "AB", "2", 1, 1)

[ShaneMuir]

It would depend on how flexible you want the outcome to be?

You could, as Ray suggests have nested Ereplace functions but this would depend on how many specific replaces you have.

A few more things would need to be answered first? Will multiple replaces for different values be required within the same string, ie would you ever need to replace both ' A ' and/or 'A ' or ' AB ' in the same source string?
Is this a static number of replacements? Or does the required replace string change frequently?

If it is a relatively few replacements and they are fixed values then I would go with Ray's suggestion.

If it is a large number of substrings that you are looking to replace then it can be done, using a reference list of values and some stage variables. Its not pretty either.
I did something similar in v8.7 where we had to find and replace several values within a string, based on thousands of different combinations.

Basically you would need to get all the possible replacement values for the given input row, and then in the transformer stage, using several variables
1. Check if the string to be replaced exists in the string
2. Get the start position of that string
3. Remove that part of the input string using the start position and the length of string to be replaced.
4. insert the new value into the string, using the start position as calculated previously

You would need to do this check for every possible replacement value. Then output the last row in the group, and you should have a new string.

Also I am not sure if there is anything that would make this easier in v11. Maybe you could do some sort of hybrid between looking up the values and then using a loop condition to call Ereplace for each value. THis would be relatively simple. Have a delimited list of values you want to replace in one string and the values to replace then in another string. Add those columns to each input row.
In a loop variable
Ereplace(Input.String,ReplaceMe[Delimter,@ITERATION,1],Value[Delimter,@ITERATION,1])

That is all hypothetical though

[devsonali]

Thank you Ray and Shane.

I have a few of these conversions (replacements) so I can choose to use the change or ereplace , however , i wanted to see if something like looping will work too , that way , I can perform a search and replace
For example
Instead of
STRING 1 = 'A SAMPLESTRING'
the string is
STRING 1 = 'A SAMPLE AB STRING'
Then I get like "1 SAMPLE 2 STRING"

And yes the replacements do not change but the number of potential replacements within a string is not exactly one all the time

If its not much of a trouble can you explain
" Have a delimited list of values you want to replace in one string and the values to replace then in another string. Add those columns to each input row.
In a loop variable
Ereplace(Input.String,ReplaceMe[Delimter,@ITERATION,1],Value[Delimter,@ITERATION,1]) "

in terms of my above example
(P.S - I am yet to see ray's full response but assume that its nested ereplace)

Thank you

[ArndW]

How about using

Code: Select all

TRIM(EREPLACE(EREPLACE(" ":TRIM(In.Col):" "," A "," 1 ")," B "," 2 "))

Would that fulfill your conversion rules for an input of In.Col?

[ShaneMuir]

If there are only a few static values which need replacing then use the nested Ereplace option as its much easier. I would only bother with my other suggestion if there are a lot of values to replace

I was thinking that you could have a delimited list of values to look for, and a delimited list of values to replace eg: (with pipe delimiter)

REPLACESTRING = ' A | A|A | AB | AB|AB '
REPLACEVALUES = '1|1|1|2|2|2'

These values would be added to each input row via a lookup/merge/join or could be static values in a stage variable.

In your transformer set a stage variable to get a count of the replacements:
svDcount = DCount(REPLACESTRING,'|')

In the loop conditions:
loop while: @ITERATION<=svDcount
variables:
lvReplace = If @ITERATION=1 then Ereplace(Input.String,REPLACESTRING['|',@ITERATION,1],REPLACEVALUES[|,@ITERATION,1]) else Ereplace(lvNewString,REPLACESTRING['|',@ITERATION,1],REPLACEVALUES[|,@ITERATION,1])
lvNewString=lvReplace

Hope that helps

[devsonali]

That helps . Thank you Shane and ArndW.
I was infact already doing a space (as suggested by Arnd)to lower the number of checks , I still wanted to test the looping (as I was doing it for the first time)

This works fine , thank again for all your help.