3 Replies Latest reply on Jan 19, 2017 9:15 PM by Shinichiro Murakami

    How do I group a dynamic formula as a dimension

    Saul Weiner

      I have two blended data sources. I have data in my primary that blends on phone number to match my secondary data source (see attached for details). My primary data source has records that match the secondary.

       

      PRIMARY

      .

      SECONDARY

      .

      Name

      Phone Number

      Phone Number

      Zip Code

      Peter Parker

      9175558282

      9175558282

      10001

      Flava Flav

      4155558714

      9735552314

      10001

      Bruce Banner

      9175558283

      9175558283

      10002

      Albert Einstein

      9175555725

      9175558283

      10001

      Bruce Wayne

      9175556745

      9175558282

      10002

      Johnny B. Goode

      2125558000

      2125558000

      10002

      Ivor Hart

      9175558281

      9175558281

      10024

       

       

      I want to create a dimension that groups all matched records from the primary to the secondary data source that meets the criteria that the phone number must start with 917 e.g. both matched once and multiple times (i.e. “*”). And then I want to group as a dimension value all unmatched records (i.e. null values) and records that matched that do not meet the criteria.

       

      Overall Match Status

      Count

      Number of Records

      Total

      7

      Number of Records

      Matched

      3

      Number of Records

      Not Matched

      4

       

      So, I created a dynamic formula field that tries to do this (called Overall Match Status. Formula is below. Will only set as a measure, and I cnanot change to a dimension), but I can’t remove the dimension that figures out the original match [called “Phone Numbers (Starting in 917) Match Status”] from row dimension as I would like. How can I just see the dimension groupings without any of the details (e.g. “Phone Numbers (Starting in 917) Match Status” dimension grouping).

       

      What’s wrong with the table below is that I don’t want two rows showing ‘matched’ and 2 rows showing ‘not matched’. I just want one row for each (like the table above)

       

      Overall Match Status

      Phone Numbers (Starting in 917) Match Status

      Count

      Number of Records

      Total

      Total

      7

      Number of Records

      Matched

      Matched Once

      1

      Number of Records

      Matched

      *

      2

      Number of Records

      Not Matched

      <NULL>

      3

      Number of Records

      Not Matched

      Phone Number matched, but did not start in 917

      1

       

       

      ------------------------------------------------------------------

      IF ISNULL(ATTR(Phone Number].[SECONDARY DATA SOURCE])) THEN "Not Matched"

      ELSEIF ATTR([Phone Number].[ SECONDARY DATA SOURCE]) = 0 THEN "Not Matched"

      ELSE "Converted" END