1 Reply Latest reply on Sep 15, 2016 10:39 AM by Patrick A Van Der Hyde

    Best practice?  Crosswalk vs Calculated Field for Setting Status

    kevin knorpp

      Hello, community


      I am currently re-designing a seasonal application that is used once per year for several months and while I had the hood up was considering whether I should change how I am determining status.


      Last year, I determined status using a calculated field with several IF statements which essentially set a Status field based on the values of several status-related fields.


      Thus, if status field 1 = this and status field 2 = that, etc., then set Status = 'Done' or whatever.


      This worked rather well and was fairly easy to change as needed but some of the changes were required because Status was null because all possible values and combinations of values in the various status-related fields were not covered by the rules provided by business partners.


      What I am considering for this year is to instead using a xref table keyed off of the various status fields with one field giving the "answer" or status given that combination of fields.


      So table columns would be status field 1, status field 2, etc, status field N, Status


      I would join this status table to my fact table to get my status.


      I see advantages of this approach being that would force identification of possible values and combinations and more accurate definition of statuses.


      I see disadvantages of this approach being possibly more difficult to change rules because would need to re-produce status xref using SQL vs using calculated field.


      Anyway, I hope this makes sense and my main question to the community is whether others have faced this decision and which way they went and what pros and cons are (including performance considerations maybe?) and/or what is considered best practice.


      Many thanks in advance

        • 1. Re: Best practice?  Crosswalk vs Calculated Field for Setting Status
          Patrick A Van Der Hyde

          Hello Kevin,


          What about using a  Parameter for the user to select and then doing the xref in a calculated field.


          The parameter and the associated calculated field to determine what to do based on each selection might be easier to setup and maintain then a xref table.   It would still be manual and you would have to account for each possibility but it might be easier and potentially perform faster for you if that xref table is quite big. 


          I hope that helps a bit,