Financial Consolidation and Close

Get Involved. Join the Conversation.

Topic

    KI
    IGNORE Mappings in SQLAnswered
    Topic posted October 10, 2019 by KIRed Ribbon: 250+ Points, tagged Data Integration 
    45 Views, 3 Comments
    Title:
    IGNORE Mappings in SQL
    Summary:
    Error exporting to FCCS when mapping to IGNORE utilizing SQL scripting
    Content:

    Was hoping someone could help with this.  I setup Data Load Mapping using SQL script, if the criteria isn't met I have it map to 'IGNORE'.  In Workflow all appears to be working (no validation errors and all the accounts are mapped properly) however when I export the data the it errors out saying 'Fetch of Driver Member "IGNORE" failed.'  All the data loads properly however the process fails which is unacceptable.  I know this was a bug in the past since the VALID_FLAG did not get updated when using SQL scripts which mapped to ignore so I included the code to set the VALID_FLAG to 'I'.  (I can't see if it is working since I can't see that field in DM).

    Has anyone experienced this?  Any clues how to get around it?  

    Version:
    19.10.55

    Best Comment

    Wayne Paffhausen

    Hello Ki,

    It is most likely due to the valid flag not being set to Ignore.  As you have noted this was a bug and you did need to set the valid flag in the #SQL map.  The issue probably comes down to the #SQL not being properly written to make this happen.

    The criteria to set the VALID_FLAG to ignore is always going to be the same as what you used to apply the mapping.  For example:

    CASE WHEN ACCOUNT LIKE '%KI%' AND ENTITY LIKE '%FCCS%' THEN 'IGNORE' END,
    VALID_FLAG = CASE WHEN ACCOUNT LIKE '%KI%' AND ENTITY LIKE '%FCCS%' THEN 'I' ELSE VALID_FLAG END
    

    The VALID_FLAG line is a bit tricky depending on what dimension your map is on and everything else.  If you don't set it to I (for ignore) then you need to set it to the current value of VALID_FLAG.  If you assume any other value you could be also causing the problem you see.

    Hopefully this helps,
    Wayne

    Comment

     

    • Ankit kumar Shrivastava

      Hi KI,

      Could you post the screenshot of your #SQL map as well as attach the log(level 5) please?

      Thanks

      Ankit Shrivastava

    • Wayne Paffhausen

      Hello Ki,

      It is most likely due to the valid flag not being set to Ignore.  As you have noted this was a bug and you did need to set the valid flag in the #SQL map.  The issue probably comes down to the #SQL not being properly written to make this happen.

      The criteria to set the VALID_FLAG to ignore is always going to be the same as what you used to apply the mapping.  For example:

      CASE WHEN ACCOUNT LIKE '%KI%' AND ENTITY LIKE '%FCCS%' THEN 'IGNORE' END,
      VALID_FLAG = CASE WHEN ACCOUNT LIKE '%KI%' AND ENTITY LIKE '%FCCS%' THEN 'I' ELSE VALID_FLAG END
      

      The VALID_FLAG line is a bit tricky depending on what dimension your map is on and everything else.  If you don't set it to I (for ignore) then you need to set it to the current value of VALID_FLAG.  If you assume any other value you could be also causing the problem you see.

      Hopefully this helps,
      Wayne

      • KI

        Thank you Wayne.  I had to reorder the SQL VALID_FLAG section and now it works.  It's such an old bug, would be great if it was fixed.