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

# How do I group a dynamic formula as a dimension

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 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

• ###### 1. Re: How do I group a dynamic formula as a dimension

Hello Saul,

Instead of data blend, I use cross-database joins to deal with it.

Integrate your data with cross-database joins in Tableau 10 | Tableau Software

Regards.

Lei

• ###### 2. Re: How do I group a dynamic formula as a dimension

Thanks Lei.

The issue I have is that we don't want to do a join as they're 2 Tableau Server Data Sources

Is there a way to do this without joins?

-Saul

• ###### 3. Re: How do I group a dynamic formula as a dimension

Hi Saul

I'm not sure I understand your request correctly, anyways.

[Converted Count]

if [Status]="Converted" then 1 end

[Not Matched Count]

if [Status]="Not Matched" then 1  end

[Count Status]

if first()=last() then total(sum([Number of Records]))

elseif [Status]="Converted" then window_sum([Converted Count])

elseif [Status]="Not Matched" then window_sum([Not Matched Count])

end

[Display Filter]

if index()=1 or [Status]<>lookup([Status],-1) then "show" else "hide" end

Thanks,

Shin

1 of 1 people found this helpful