1 Reply Latest reply on Jan 4, 2018 2:01 PM by Yuriy Fal

    Data blending/calculation issue

    John Farzing

      Hello,

       

      I am having an issue blending two data sources. The main issue seems to be with the linking fields. In my secondary source, if there is a matching city, I need to use the field "City Marker" if no matching city, but matching state, I need to use the field "State Marker" else I need to use 1.02. This is how I have that calculation set up:

       

      If not ISNULL([City]) then [City Marker] elseif not ISNULL([State]) then [State Marker] else 1.02 END

       

      However, the issue is that my secondary data source is set up like this where there is no consistent linking field:

       

       

      If anyone could help me blend this data correctly I would greatly appreciate it. I tried using a join, but that caused other problems so I'm hoping to accomplish this with a blend, if possible. I have included a sample workbook. Thank you

        • 1. Re: Data blending/calculation issue
          Yuriy Fal

          Hi John,

           

          You may want to blend with

          the two distinct Secondary datasources --

          each one on either [City] or [State].

           

          The results may be looking counter-intuitive (or even weird).

           

          For example when 'rolling-up' (removing) [City] --

          and leaving only the [State] dimension on a view --

          the calculation [YF : City Marker] gives 1.11 for the Colorado

          (which is likely to come from the [City] == 'Denver').

           

          This could bring the confusion later --

          when referring the field in other calcs.

           

          To make it a bit simple, one could be using joins instead.

          Joining the 'lookup table' twice -- ON City and ON State --

          would allow the calculation to be a Row-Level.

           

          Please find the attached.

           

          Yours,

          Yuri