8 Replies Latest reply on Mar 22, 2013 1:58 AM by Frederick Lai

# Categorizing line items multiple times

I am trying to create a fee schedule table by with the Net Fee to each broker by completion date.

Example raw data in excel file:

ClientGross Fee to Company
Support Broker
Support Broker Net Fee
Completion Date
Honda\$1000Alan\$800Benny\$200Jan 2013
Toyota\$3000Alan\$2000Charles\$1000Jan 2013
Mazda\$5000Charles\$5000

Feb 2013
Nissan\$2500Benny\$2000Alan\$500March 2013

I have created a "Broker" calculated field to group and categorize the broker:

if [Lead Broker] = "Alan" or [Support Broker] = "Alan" then "Alan"

elseif [Lead Broker] = "Benny" or [Support Broker] = "Benny" then "Benny"

elseif [Lead Broker] = "Charles" or [Support Broker] = "Charles" then "Charles"

end

Created "Broker Net Fee" calculated field based on "Broker" field calculated above:

if [Broker] = [Lead Broker] then [Lead Broker Net Fee]

elseif [Broker] = [Support Broker] then [Support Broker Net Fee]

end

However, my results show that each line item will be assigned by the first "match" according to the order of the code in the "Broker" calculated field, which means that I am not capturing the full information required to produce my fee schedule table.

i.e. Tableau Output will produce something like this (in black)

BrokerJan Net Fee
Feb Net Fee
March Net Fee
Alan\$800 + \$2000 = \$2800

Benny

\$2000
Charles
\$5000

What I want is below:

BrokerJan Net Fee
Feb Net Fee
March Net Fee
Alan

\$800 + \$2000 = \$2800

\$500
Benny\$200
\$2000
Charles\$1000\$5000

Can anyone please help me with solutions on how to solve this? Thanks and much appreciated!

• ###### 1. Re: Categorizing line items multiple times

Can anyone advise? Any help would be most appreciated, thanks!

• ###### 2. Re: Categorizing line items multiple times

Hi Lai,

I was stuck in your problem but with some tricks i created what you wanted but for a large database this is not a good solution. Please find my attached File.

1 of 1 people found this helpful
• ###### 3. Re: Categorizing line items multiple times

Hi Guys,

I built on Prashant's example and have another solution that might work for you.

This seems like a classic case of needing to "unpivot" the source data so that each row provides information at the deal, broker level. Given you have up to two brokers / deal, you need a row for each broker. This is easy to do with custom SQL.

When you select custom SQL in Tableau, Tableau provides the baseline SQL to retrieve the full table (Excel sheet). I cut-and-pasted this into a text editor, where I added UNION ALL to the bottom and duplicated the text so that there are two rows for every existing row. Then it's just a matter of changing the column header to be a generic "Broker" and mapping "Lead Broker" to this field in the first row and "Support Broker" in the second row.

Adding a "Broker Role" field helps clarify this and also allows you to segment by role in Tableau.

SELECT [Sheet1\$].[Client] AS [Client],

[Sheet1\$].[Completion Date] AS [Completion Date],

[Sheet1\$].[Gross Fee to Company] AS [Gross Fee to Company],

[Sheet1\$].[Lead Broker] AS [Broker],

[Sheet1\$].[Lead Broker Net Fee] AS [Broker Net Fee],

"Lead" AS [Broker Role]

FROM [Sheet1\$]

UNION ALL

SELECT [Sheet1\$].[Client] AS [Client],

[Sheet1\$].[Completion Date] AS [Completion Date],

[Sheet1\$].[Gross Fee to Company] AS [Gross Fee to Company],

[Sheet1\$].[Support Broker] AS [Broker],

[Sheet1\$].[Support Broker Net Fee] AS [Broker Net Fee],

"Support" AS [Broker Role]

FROM [Sheet1\$]

• ###### 4. Re: Categorizing line items multiple times

Thank you so much Prashant and Jim!

Jim:

I'm quite unfamiliar with custom SQL, am I correct in assuming that if I have more than 1 "Support Broker" I have to duplicate the text for the extra broker? For example, if I have 4 support brokers, do I duplicate the paragraph 4 times instead of once in your example for 1 support broker?

Extra question: what does "UNION ALL" do?

• ###### 5. Re: Categorizing line items multiple times
If I have more than 1 "Support Broker" I have to duplicate the text for the extra broker?

How is this done with the source data?  In your original example, it looked like you have at most two brokers per deal, primary and support. If it's common to have more than one support broker or even co-primary brokers, then you might want to rethink the format of your source data, so that it looks like the custom-SQL version---one row per broker-deal.

Extra question: what does "UNION ALL" do?

UNION ALL combines two SELECT statements, provided each SELECT statement has the same number (and type) of columns. In the case of reshaping data, it's commonly used to "duplicate" a table / combine columns. For example, if had "wide" data

Name Weight_Wk1 Weight_Wk2

Jim                      72                  68

Cathy                  65                  66

We could make this "long" by reshaping with a union all. In this case the long format is better for Tableau---since we want to plot weight vs time---but worse for human consumption.

// All column names are identical for each select statement

SELECT [Sheet1\$].[Name] as [Name]

"Week 1" as [Weight Time]

[Sheet1\$].[Weight_Wk1] as [Weight Value]

FROM [Sheet1\$]

UNION ALL

SELECT [Sheet1\$].[Name] as [Name]

"Week 2" as [Weight Time]

[Sheet1\$].[Weight_Wk2] as [Weight Value]

FROM [Sheet1\$]

Name Weight Time     Weight Value

Jim     Week 1             72

Jim     Week 2             68

Cathy  Week 1            65

Cathy  Week 2            66

1 of 1 people found this helpful
• ###### 6. Re: Categorizing line items multiple times

Thanks Jim, understood.

Now I'm running into trouble of double counting the stuff that I don't want to double count.

Say for example if there was another column that showed the Order Quantity

E.g. Honda has an "Order Quantity" = 10

If I want to do an analysis per "Client", it would show a sum of 20 for Order Quantity, because each deal is now separated into two line items. How do I now keep everything from double counting for other columns in my spreadsheet?

• ###### 7. Re: Categorizing line items multiple times

Yes, it can get a little messy. Depending on how big your project is, you may want to take a different approach (such as using multiple tables at different levels of granularity).

But an easier path, if you still have only one primary broker, is to just add a filter on these sheets so that only primary broker records are used.

1 of 1 people found this helpful
• ###### 8. Re: Categorizing line items multiple times

Jim, you're a legend. Thanks once again for the prompt and helpful response - it is much appreciated