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) v...
Admin
  • MarcusH's avatar
    MarcusH
    17 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.

MarcusH's avatar
MarcusH
Valued Contributor
17 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.