Skip to Content
0
Jun 19, 2018 at 07:49 PM

Business Object XI Variable SubStr formula

139 Views Last edit Jun 19, 2018 at 07:47 PM 2 rev

Currently using SAP Business Objects Desktop Intelligence XI

Scenario 1:

Would like to create a variable to compare two different column values (Equipment ID and Group ID) and indicate "Match" and "Don't Match". However I only want it to compare a specific section between both ID's.

For example data:

Equipment ID Group ID Column Match/No Match (Variable)

56789-DAD-005-1 56789-DAD-GP1 MATCH

67894-DAD-001-9 67894-DAD-GP3 MATCH

78921-CA-009-5 78921-CAD-GP5 NO MATCH

98715-DA-001-8 98715-DA-GP1 NO MATCH*

Variable:

= If SubStr(<Equipment ID)> ,1 ,10)=SubStr(<Group ID> ,1 ,10) Or IsNull(<Group ID>)Then "Match" Else "No Match"

The Variable tried to use was with SubStr that would evaluate at 1st Character and would evaluate only 9 characters length for evaluation. Formula works, but there are cases where the second set of data will not be the full four characters length, some will be short or the opposite happens where the second set will be much longer than three character length and would need to have that evaluated. *As it is from above the formula would tag 98715-DA as No Match due to the character length of evaluation to be 10 character, I need the formula to stop at the second set of hyphen.

Is there a way where I can change the Variable to perhaps evaluate as the first five and after the hyphen it would continue to evaluate to the second hyphen and wouldn't matter how many characters are in the second set of hyphen? Example "?????-*- "

Also the IsNull was put there for the situation that some Equipment will not be grouped and couldn't figure to just ignore or bypass so just instead include it but IsNull would be a match. Usually after that I can use a filter out blanks, but if there is a way to get that in there as well would be great, but I am more concerned with the above.

Any help or guidance is greatly appreciated!!!