Forum Discussion

Russ's avatar
Russ
New Contributor II
10 months ago

Range Transformation Rule not working

Hi Community,

I am using a range transformation rule to give all balance sheet accounts a Flow of CB_Load. In the data source Flow is pointing the account number. I then use account ranges in the Flow transformation group to derive the Target Value of CB_Load. However, I am getting a validation error because Flow is reverting to None which is against the constraint settings for these accounts.

I would expect this account (8100030) to derive a Flow of CB_Load due to Rule Name Range4 in the screenshot.

However the Range rule is ignored and Flow is stamped with None as per the Mask pass through rule.

Does anybody have an idea of what might be going wrong? I could just have a hundreds of pages of one to one mappings but this would be a nightmare to maintain!

Please note that the account transformation rule is a mask pass through where Rule Expression = * and Target Value = * so in the above example 8100030 is also the account in the data source.

Thanks

Russ

Transformation
  • MarcusH's avatar
    MarcusH
    Contributor III
    10 months ago

    Range4 has 7 numbers in the starting code and 8 numbers in the end. I seem to remember from somewhere that the start and end items must be the same length although that might be fixed or apply to another product entirely.

    I would change Range4 to 8000010~8120000.

    Account 8100030 should fall into that range then. 

    • Russ's avatar
      Russ
      New Contributor II
      10 months ago

      Hi Marcus,

      Thanks for your response. I was thinking along the same lines so changed the mapping so that upper and lower limits are the same length. However, it's still not working as expected. 

      Account 8010000 (8 characters) does not fall within the ranges below but Flow is being mapped as CB_Load. My guess is that Rule Name BSRange6 below is driving this. Although the range upper and lower limits are 7 characters the first 3 characters in the above account (801) fall within 800-999 (first 3 characters of upper and lower limits of BSRange6). This behaviour is how you would expect numbers stored as text work.

       

      Any other suggestions? I've tried leading zeros but this doesn't work either.

      Thanks,

      Russ

       

      • MarcusH's avatar
        MarcusH
        Contributor III
        10 months ago

        8010000 is 7 characters so Range6 is working correctly. If that is just a typo and it is actually 8 chars it would help if you can identify definitely which map is being used. Select the row in the Import stage and right click then select 'View Transformation Rules' it will tell which map is being used. You can always use the Order column to change the processing order.

  • MarcusH's avatar
    MarcusH
    Contributor III
    10 months ago

    It looks like you will need to pad the Account dimension on the Data Source. Set the Leading Fill Value to 00000000 which will then pad out any source Accounts with zero so they are all 8 characters long. Then adjust your ranges so that they take into account the leading zeroes so the map for Range6 will be 08000010~09999999.