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

    Categorizing line items multiple times

    Frederick Lai

      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
      Lead BrokerLead Broker Net Fee
      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
          Frederick Lai

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

          • 2. Re: Categorizing line items multiple times
            Prashant Sharma

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

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

               

               

              options.png

              • 4. Re: Categorizing line items multiple times
                Frederick Lai

                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
                  Jim Wahl
                  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
                    Frederick Lai

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

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

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