Skip to Content

SAP BO how to substract a specific number from a concatenated text

Hi everyone,

recently I came across this post:

https://archive.sap.com/discussions/thread/3245018

By doing this, I managed to concatenate various entries into one cell that were previously displayed in different rows.

Lets assume I work for a production company and I have a dimension which includes the main part number of a product. Also, I have a second dimension which includes all part numbers (the main part number and all other part numbers relevant) for the product. For this second Dimension, I managed to concatenate them into one cell and separate them with a comma. Now it is desired that I substract the "main part number" from the "all part numbers".

Here is an example:

Column A (main part number)

9932

Column B (all part numbers)

5432,7865,9932

Column C (This is the column I Need)

5432,7865

I must note that the appearance of the main part in Column B is random, meaning it can be on the left, on the right or in the middle. Thus, I cannot simply cut a certain amount of characters from the left or right as it can appear on any side of the text.

Is there any way to solve this issue, e.g. to remove the extract string of Column A from Column B?

Regards,

Jonas

Add a comment
10|10000 characters needed characters exceeded

Related questions

6 Answers

  • Best Answer
    Posted on Feb 20, 2019 at 04:21 PM

    Hi Jonas,

    I have had another look at the suggested solution and detected an error in the formula which will not always return the correct result for concatenated strings with more than just three values. I am repeating the different (changed) variables and comments with this post rather than correcting the previous post of six days ago.

    So, create a set of 10 new variables, as follows:

    [All PNos Cleaned]

    =Replace([All_PNos];", ";",")

    This variable merely replaces trailing blanks after the comma separator to ensure consistency in the data set. If blanks are needed in the desired result you can add them later on with another variable.


    [# Commas]

    =Length([All PNos Cleaned]) - Length(Replace([All PNos Cleaned];",";""))

    Counts the number of comma separators to determine the position of the separators in each string (v_Pos Co1 to v_Pos Co3).


    [Length APC]

    =Length([All PNos Cleaned])

    Determines the length of the cleaned string. Required to calculate the position of the Part Number that needs to be extracted from the string / remain in the string.


    [Length Main PNo]

    =Length(RightTrim([Main_PNo]))

    Determines the length of the Main Part Number (removing any trailing blanks). Required to calculate the string comparison in each [ExtractX] variable as well as the length and position of the Main Part Number that is removed with the [ExtractX] variables.


    [v_Pos Co1]

    =If([# Commas]>=1) ThenPos([All PNos Cleaned];",")

    Returns the position of the first comma in the string.


    [v_Pos Co2]

    =If([# Commas]>=2) Then Pos(Right([All PNos Cleaned];([Length APC]-[v_Pos Co1]));",")+[v_Pos Co1]

    Returns the position of the second comma in the string.


    [v_Pos Co3]

    =If([# Commas]>=3) Then Pos(Right([All PNos Cleaned];([Length APC]-[v_Pos Co2]));",")+[v_Pos Co2]

    Returns the position of the third comma in the string.


    Next, I created another set of helper variables, each intended to remove the unwanted Main Part No. from the concatenated string:

    [Extract1]

    =If([# Commas]>=1 And (Right([All PNos Cleaned];[Length Main PNo]) = [Main_PNo])) Then Left([All PNos Cleaned];(([Length APC]-1)-[Length Main PNo]))

    ElseIf([# Commas]>=1 And (Left([All PNos Cleaned];([v_Pos Co1]-1)) = [Main_PNo])) Then Right([All PNos Cleaned];([Length APC]-([Length Main PNo]+1)))

    This variable first checks whether the concatenated string consists of two or more part numbers. If yes it removes the main part number from the beginning or the end of the string.

    [Extract2]

    =If([# Commas]=2 And (Substr([All PNos Cleaned];([v_Pos Co1]+1);[Length Main PNo]) = [Main_PNo])) Then Left([All PNos Cleaned];[v_Pos Co1])+""+Right([All PNos Cleaned];([Length APC]-[v_Pos Co2]))

    This variable first checks whether the concatenated string consists of three part numbers and then removes the main part number from between the two commas in the string. If the main part number sits at the beginning or the end of the string it is removed via [Extract1] variable.

    [Extract3]

    =If([# Commas]=3 And (Substr([All PNos Cleaned];([v_Pos Co1]+1);[Length Main PNo]) = [Main_PNo])) Then Left([All PNos Cleaned];[v_Pos Co1])+""+Right([All PNos Cleaned];([Length APC]-[v_Pos Co2]))

    ElseIf([# Commas]=3 And (Substr([All PNos Cleaned];([v_Pos Co2]+1);[Length Main PNo]) = [Main_PNo])) Then Left([All PNos Cleaned];[v_Pos Co2])+""+Right([All PNos Cleaned];([Length APC]-[v_Pos Co3]))

    This variable first checks whether the concatenated string consists of four part numbers and then removes the main part number from either between the first two commas in the string, or the last two commas in the string. If the main part number sits at the beginning or the end of the string it is removed via [Extract1] variable.

    Before moving on to the final variable, create two more helper variables which are needed to validate the correct extraction syntax for strings consisting of two concatenated values only:

    [Main Left]

    =[# Commas]>=1 And (Left([All PNos Cleaned];([v_Pos Co1]-1)) = [Main_PNo])

    [Main Right]

    =[# Commas]>=1 And (Right([All PNos Cleaned];[Length Main PNo]) = [Main_PNo])

    The final variable [Other Part Nos] is defined as follows. Note that where there are no commas in the string there is only one part number which is equal to the main part number and thus needs to be removed from the string (my assumption). Finally, we use Replace() to add the blanks back behind each comma:

    [Other Part Nos]

    =Replace(

    (

    If([# Commas]=0) Then “”

    ElseIf([# Commas]=1 Or [Main Left]=1 Or [Main Right]=1) Then [Extract1]

    ElseIf([# Commas]=2) Then [Extract2]

    ElseIf([# Commas]=3) Then [Extract3]

    )

    ;",";", ")

    If you have more than four part numbers in your concatenated string you will need to add comma position variables and extract variables accordingly.

    As a final thought, have you considered concatenating your fields AFTER doing a comparison between your different values? This requires all fields containing the part numbers that make up your original concatenated string “All Part No.” to be pulled into your report. In my example this would be fields PN1, PN2, PN3 and PN4:

    [Other Part Nos]

    =If([Main_PNo]=[PN1]) Then Concatenation(Concatenation([PN2];[PN3]);[PN4])

    ElseIf([Main_PNo]=[PN2]) Then Concatenation(Concatenation([PN1];[PN3]);[PN4])

    ElseIf([Main_PNo]=[PN3]) Then Concatenation(Concatenation([PN1];[PN2]);[PN4])

    ElseIf([Main_PNo]=[PN4]) Then Concatenation(Concatenation([PN1];[PN2]);[PN3])

    Then create another helper variable which we need for the "clean up" variable [Other Part Nos Cleaned] that removes unwanted trailing commas:

    [LenOPs]

    =Length([Other Part Nos.])

    [Other Part Nos Cleaned]

    =If(Right([Other Part Nos.];4)=", , ") Then Replace([Other Part Nos.];", , ";"")

    ElseIf(Right([Other Part Nos.];2)=", ") Then Left([Other Part Nos.];([LenOPs]-2))

    Else [Other Part Nos.]

    Looking at the complexity of the first solution I would suggest you go with the second approach.

    In general, nested variables will always create load on calculating your results at report level. Hence, I recommend delegating such calculations to your database directly or via your universe (if you are using universes). If neither is possible, you may have to consider performance issues. Your latest comment suggests you are retrieving larger datasets. Can you share your error messages?

    Kind regards,

    Tom

    PS. See the results in the attached screenshots:

    751132sola.png

    751132solb.png


    751132sola.png (55.0 kB)
    751132solb.png (59.9 kB)
    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Tom,

      thank you for your reply :) I tried to implement these variables but I receive errors when I implement the Extract variables. I am still quite new to it, so I apologize for any super simple question.

      E.g. let's look at [Extract1]

      This is your solution.

      =If([# Commas]>=1 And (Right([All PNos Cleaned];[Length Main PNo]) = [Main_PNo])) Then Left([All PNos Cleaned];(([Length APC]-1)-[Length Main PNo]))

      ElseIf([# Commas]>=1 And (Left([All PNos Cleaned];([v_Pos Co1]-1)) = [Main_PNo])) Then Right([All PNos Cleaned];([Length APC]-([Length Main PNo]+1)))

      So I changed small things (parantheses and lenghts operator) which BO marked as errors in the formula and get to this:

      =If([# Commas]>=1 And (Right([All PNos Cleaned];Length([Main_PNo]) = [Main_PNo]))) Then Left([All PNos Cleaned];(([Length APC]-1)-Length([Main_PNo])))

      ElseIf([# Commas]>=1 And (Left([All PNos Cleaned];([v_Pos Co1]-1)) = [Main_PNo])) Then Right([All PNos Cleaned];([Length APC]-Length([Main_PNo]+1)))

      After that, I receive an error for the part of the formula marked in bold which states:

      The expression or sub-expression at position 69 in the '=' function uses an invalid data type. (IES 10037)

      I googled and the error is connected to the data type of the variables. Do you know what to do about this error?

      Best regards,

      Jonas

  • Posted on Feb 13, 2019 at 04:12 PM

    Hi Jonas,

    Thanks for uploading the sample set. In the meantime, I had a look at a different way to achieve this and I believe that the following approach will lead to the desired solution. This is modelled on your original example:

    Main_PNo...All_PNos..............................Wanted_PNs
    9932.......5432,7865,9932........................5432,7865
    5432.......5432,7865,9932........................7865,9932
    7865.......5432,7865,9932........................5432,9932
    9932.......9932 H, 9932 O, 345678, 9932, 5231....9932 H, 9932 O, 345678, 5231 

    Create a number of new variables, as follows:

    [All PNos Cleaned]

    =Replace([All_PNos];", ";",") 

    This merely replaces trailing blanks after your comma seperator. If these are needed in the desired result you can add them later on with another variable.



    [# Commas]

    =[Length All PNos Cleaned] - Length(Replace([All PNos Cleaned];",";"")) 

    Counts the number of comma seperators to determine the position of the sperators in each string (v_Pos Co1 to v_Pos Co4).


    [Length APC]

    =Length([All PNos Cleaned]) 

    Determines the length of the cleaned string. Required to calculate the positions of the PNs that need to be extracted from the string / remain in the string.


    [v_Pos Co1]

    =If([# Commas]>=1) Then Pos([All PNos Cleaned];",") Else [Length APC] 

    Returns the position of the first comma in the string.


    [v_Pos Co2]

    =If([# Commas]>=2) Then Pos(Right([All PNos Cleaned];([Length APC]-[v_Pos Co1]));",")+[v_Pos Co1] Else [Length APC] 

    Returns the position of the second comma in the string.


    [v_Pos Co3]

    =If([# Commas]>=3) Then Pos(Right([All PNos Cleaned];([Length APC]-[v_Pos Co2]));",")+[v_Pos Co2] Else [Length APC]+1 

    Returns the position of the third comma in the string.


    [v_Pos Co4]

    =If([# Commas]>=4) Then Pos(Right([All PNos Cleaned];([Length APC]-[v_Pos Co3]));",")+[v_Pos Co3] Else [Length APC]+1 

    Returns the position of the fourth comma in the string.


    [Full Extracted String]

    =RightTrim((If([Main_PNo]=Substr([All PNos Cleaned];1;[v_Pos Co1]-1)) Then Right([All PNos Cleaned];([Length APC] - [v_Pos Co1])) 
    ElseIf([Main_PNo]=Left([All PNos Cleaned];[v_Pos Co1]-1)) Then Right([All PNos Cleaned];[Length APC]-[v_Pos Co1])
    ElseIf([Main_PNo]=Substr([All PNos Cleaned];[v_Pos Co1]+1;[v_Pos Co2]-[v_Pos Co1]-1)) Then Left([All PNos Cleaned];[v_Pos Co1])+""+Substr([All PNos Cleaned];[v_Pos Co2]+1;[Length APC]) 
    ElseIf([Main_PNo]=Substr([All PNos Cleaned];[v_Pos Co2]+1;[v_Pos Co3]-[v_Pos Co2]-1)) Then Left([All PNos Cleaned];[v_Pos Co2]-1)+""+Substr([All PNos Cleaned];[v_Pos Co3];[Length APC]) 
    ElseIf([Main_PNo]=Substr([All PNos Cleaned];[v_Pos Co3]+1;[v_Pos Co4]-[v_Pos Co3]-1)) Then Left([All PNos Cleaned];[v_Pos Co3]-1)+""+Substr([All PNos Cleaned];[v_Pos Co4];[Length APC]) 
    ElseIf([Main_PNo]=Right([All PNos Cleaned];([Length APC]-[v_Pos Co4]))) Then Left([All PNos Cleaned];[v_Pos Co4]-1)))
    

    This formula replaces the not desired [Main_PNo] from the concatenated string [All PNos]. It looks for a matching value at the left and right 'columns' of the string, as well as in 'columns' 2, 3 and 4 - all of these determined via the comma position calculation for the relevant position of the matching value.

    If you concatenate more fields than just the five in your example you will need to create additional "v_Pos_CoX" variables and amend the "Full Extracted String" accordingly by adding additional ElseIf statements.

    You can also create a variable to check for the correctly returned extracted strings (comparing to Wanted_PNs): =[Full Extracted String]=Replace([Wanted_PNs];", ";",")

    I have done some testing on this and I am pretty confident this works. Maybe you can test against your set?

    Kind regards,

    Tom

    Add a comment
    10|10000 characters needed characters exceeded

    • Hello Tom,

      thank you very much for your comprehensive answer to my initial question, I apologize for the late reply.
      I am currently struggling to run the last variable as it seems that my SAP BO cannot handle it and always leads to server errors - quite massive formula. I will try to somehow fix this issue or possibly split up the last variable if thats possible.

      I will try to run it by tomorrow and then accept your solution :)

      Best regards,
      Jonas

  • Posted on Feb 12, 2019 at 09:16 AM

    Hi Jonas,

    Creating two new variables should do the trick for you:

    [Identify]

    =Replace([All_PNos];[Main_PNo];"Remove")

    [Other PNs]

    =If(Left([Identify];7)="Remove,") Then Right([Identify];(Length([Identify])-7)) ElseIf(Right([Identify];7)=",Remove") Then Left([Identify];(Length([Identify])-7)) Else Replace([Identify];",Remove,";",")

    This should also work for any other combination of part numbers in your concatenated string, i.e. no matter how many part numbers you have in your string.

    Kind regards,

    Tom


    751132.png (7.9 kB)
    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Tom, thank you for your help.

      The solution you suggested works for some rows but not for all. I observed that it only works if the "main part number" is on the left side of the string of the "all part numbers".

      I should have mentioned that the strings can have different lengths, so not all part numbers have the same number of characters.

      Also, sometimes only an additional letter is different, e.g. 9932 as the main - and 9932 J as one of the all part numbers is displayed so that we accidently replace everything but the "J" as the part numbers start with the same characters.

      Moreover, the number of parts in the "all part numbers" cell can vary from 1 to ~15, not only 3 as in the Initial example.

      So, an example of part numbers could also be:

      Column A (main part number)

      9932

      Column B (all part numbers)

      9932 H, 9932 O, 345678, 9932, 5231

      Column C (desired result)

      9932 H, 9932 O, 345678, 5231

      The issue is therefore quite tricky - not sure whether there is a possible solution or not.

      Thank you,

      Jonas

  • Posted on Feb 12, 2019 at 03:30 PM

    Hi Jonas,

    Maybe you can attach a bigger data sample to work with? I am confident we can find a solution that works for you.

    Kind regards,

    Tom

    Add a comment
    10|10000 characters needed characters exceeded

    • partno.txt

      Hey Tom, I enclosed a text file which is bigger and is very similar to my actual data. Excel-files aren't permitted. It includes all the troubles I have with my actual data (different character sizes of the part no., different positions within the string, similar part no. with exception of one or two letters).

      In the 4th column I should have all the part numbers excluding the main part number, e.g. the first few rows should be empty and if there are more than one part in the all part no. column, the ones which are not exactly the same as the main part no. are supposed to stay and the one which is exactly the same like the main part number should be removed.
      In case you have any idea how to solve this, please let me know. Thank you in advance you, already helped me a lot.

      Regards,

      Jonas

      partno.txt (1.4 kB)
  • Posted on Feb 21, 2019 at 09:33 AM

    Hi Jonas,

    I forgot to list another variable which is referenced in the the [Extract] variables: [Length Main PNo]. This is defined as: =Length(RightTrim([Main_PNo]))

    So if you revert back to the original syntax of [Extract1], i.e.

    =If([# Commas]>=1 And (Right([All PNos Cleaned];[Length Main PNo]) = [Main_PNo])) Then Left([All PNos Cleaned];(([Length APC]-1)-[Length Main PNo])) ElseIf([# Commas]>=1 And (Left([All PNos Cleaned];([v_Pos Co1]-1)) = [Main_PNo])) Then Right([All PNos Cleaned];([Length APC]-([Length Main PNo]+1)))

    all should be working as expected. I'll add the missing variable to my previous answer.

    Kind regards,

    Tom

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Tom, thanks for the quick reply.

      Few more questions:
      Do I need to add another variable before the following variable?

      [# Commas]

      =[LengthAll PNos Cleaned] - Length(Replace([All PNos Cleaned];",";""))

      Do I need to add the variable [LengthAll PNOs Cleaned] like Length(RightTrim([All PNOs Cleaned]) as before or did you mean to write it as length([All PNos Cleaned])?

      Moreover, before the following variable, do I need to add [Main Left] and [Main Right] as variables?

      [Other Part Nos]

      =Replace(

      (

      If([# Commas]=0) Then “”

      ElseIf([# Commas]=1 Or [Main Left]=1 Or [Main Right]=1) Then [Extract1]

      ElseIf([# Commas]=2) Then [Extract2]

      ElseIf([# Commas]=3) Then [Extract3]

      )

      ;",";", ")

      Thank you in advance.

      Best regards,

      Jonas

  • Posted on Feb 21, 2019 at 10:57 AM

    Hi Jonas,

    Apologies for additional oversights on my part, missing the additional helper variables from the previous answer...

    Yes, you can either create two further variables:

    [Main Left] =[# Commas]>=1 And (Left([All PNos Cleaned];([v_Pos Co1]-1)) = [Main_PNo])

    [Main Right] =[# Commas]>=1 And (Right([All PNos Cleaned];[Length Main PNo]) = [Main_PNo])

    or you integrate above syntax directly into [Other Part Nos]:

    =Replace(

    (

    If([# Commas]=0) Then ""

    ElseIf([# Commas]=1 Or ([# Commas]>=1 And (Left([All PNos Cleaned];([v_Pos Co1]-1)) = [Main_PNo])) Or ([# Commas]>=1 And (Right([All PNos Cleaned];[Length Main PNo]) = [Main_PNo]))) Then [Extract1]

    ElseIf([# Commas]=2) Then [Extract2]

    ElseIf([# Commas]=3) Then [Extract3]

    )

    ;",";", ")

    It looks a bit messy but avoids the creation of more helper variables.

    The correct syntax of the [# Commas] variable should be =Length([All PNos Cleaned]) - Length(Replace([All PNos Cleaned];",";"")) This was a typo and will be corrected in the main answer.

    Hope you will not find further omissions!

    Kind regards,

    Tom

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.