Forum Discussion

kivlind's avatar
kivlind
New Contributor
17 days ago
Solved

Transformation Rule Mystery

Hello all,

I have a field in my input dataset that I am tracing the mapping for and I can't figure out why One Stream is selecting the mapping rule it is using.

The field is a cost center (UD1) value of '1VA' and it is not specifically called out in one-to-one or is it included as a composite range. I really think it should have been falling out as an exception.

OneStream however tells me it is applying a Range Rule 'ABC' where the rule expression = '18~21'. 

I am flummoxed as to the reason why. I've double checked it a few times. 

Any ideas why this range rule was selected, or maybe an idea of other areas to check out to see why the cost center UD1 mapping is behaving the way it is?

Thanks in advance. Happy to add add'l details if needed.

Regards,

DK

Admin
  • MarcusH's avatar
    MarcusH
    16 days ago

    You can get some strange results when using a range map and the source has a mixture of numbers and characters. The comparison of the start and end values is treated alphanumerically which means that the length of the value to be compared is immaterial. The starting number of your range is 18 which in ASCII terms is 49 56. The ending number is 20 which in ASCII terms is 50 48. Your search is 1VA in ASCII 49 86 65. As RobbSalzmann​ explains that means 1VA does fall between 18 and 20. If you were sorting those three numbers to be displayed in a dictionary, the order would be 18, 1VA, 20. I avoid Range maps because of the potential for confusing results. I have in the back of my mind the idea that ALL Range maps are applied to unmapped source records not just the first one that matches. You could try adding the 1to1 map as RobbSalzmann​ suggests or try adding another Range map 1VA~1VZ with a sequence number greater than the 18-20 map.

2 Replies

  • MarcusH's avatar
    MarcusH
    Valued Contributor
    16 days ago

    You can get some strange results when using a range map and the source has a mixture of numbers and characters. The comparison of the start and end values is treated alphanumerically which means that the length of the value to be compared is immaterial. The starting number of your range is 18 which in ASCII terms is 49 56. The ending number is 20 which in ASCII terms is 50 48. Your search is 1VA in ASCII 49 86 65. As RobbSalzmann​ explains that means 1VA does fall between 18 and 20. If you were sorting those three numbers to be displayed in a dictionary, the order would be 18, 1VA, 20. I avoid Range maps because of the potential for confusing results. I have in the back of my mind the idea that ALL Range maps are applied to unmapped source records not just the first one that matches. You could try adding the 1to1 map as RobbSalzmann​ suggests or try adding another Range map 1VA~1VZ with a sequence number greater than the 18-20 map.

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    17 days ago

    Based on only what you've provided, this seems like a bug in how the mapping rule is processed.  Lexigraphically, 1VA is between 18 and 21:  18 < 1VA < 21.  Try adding a 1 to 1 mapping for 1VA, which has precedence over the range mapping, and see if that helps.