2 Replies Latest reply on Feb 13, 2012 5:18 AM by Vladislav Grigorov

    Reshape data via custom SQL

    Vladislav Grigorov

      Hello Tableau pros!

       

      I am running myself into a question I am not able to solve, although I have a gut feeling it has to be some kind of custome SQL. I have 2 tables in a transactional system - one containing transactions, and the other one - characteristics (dimensions) of those transactions. The problem is that in the second table you may have more than one characteristic with its value for any given transaction. (I attach a XLS file for reference). So I need to join the 2 tables and analyze the transactions by their dimension values, which are in the second table. The trick is that in the second table there are 2 fields:

      [Dimension Code], and [Dimension Value Code]. The [Dimension Code] field determines what property of the transaction is set, and [Dimension Value Code] determines the value of that property. Thus for every transaction I have a number of records in the dimensions table. Please also bear in mind that this comes from a transactional system that I cannot change - i.e. I cannot pre-shape the source data.

      I tried with some custom SQL, to convert the dimensions table to columns that I will add to every transaction. This works for a single type of [Dimension Code]. However I was not able to get my way through converting all the rows in the dimension table to columns in the transactions table - I could not write the custom SQL, or maybe there's another way.

       

      So any help with this one-to-many would be greatly appreciated.

        • 1. Re: Reshape data via custom SQL
          Russell Christopher

          Hi Vladislav -

           

          You didn't mention what RDBMS your tables are being managed by, but many (if not most) database systems have a statement which allow you to "pivot" the data, which it sounds is what you need to do. For example, SQL Server offers the PIVOT tsql statement.

           

          Here's an example: http://msdn.microsoft.com/en-us/library/ms177410.aspx

           

          Another option would be to bring the data out of the system altogether and use the Tableau data reshaper add-in (for Excel) which could pivot the data for you.

           

          Good luck!

          • 2. Re: Reshape data via custom SQL
            Vladislav Grigorov

            Hi Russell,

            My data is in MS SQL Server. It is actually MS Dynamics NAV ERP system, that I am connecting to, therefore I am not able to change the data structure outside Tableau. I did not try with PIVOT, but succeeded to make a work-around by modifying the ON clause in several join statements. Below I am putting the code from the custom SQL connection:

            ------ Code begin -----

            SELECT  [Value Entry].[Entry No_] AS [Entry No_],
              [Value Entry].[Item No_] AS [Item No_],
              [Value Entry].[Posting Date] AS [Posting Date],
              [Value Entry].[Source No_] AS [Source No_],
              [Value Entry].[Invoiced Quantity] AS [Invoiced Quantity],
              [Value Entry].[Sales Amount (Actual)] AS [Sales Amount (Actual)],
              [Value Entry].[Discount Amount] AS [Discount Amount],
              [Value Entry].[Cost Amount (Actual)] AS [Cost Amount (Actual)],
              [Item].[No_] AS [No_],
              [Item].[Item Category Code] AS [Item Category Code],
              [Item].[Product Group Code] AS [Product Group Code],
              [LED1].[Dimension Value Code] AS [AREA],
              [LED2].[Dimension Value Code] AS [BUSINESSGROUP],
              [LED3].[Dimension Value Code] AS [CUSTOMERGROUP],
              [LED4].[Dimension Value Code] AS [DEPARTMENT],
              [LED5].[Dimension Value Code] AS [PROJECT],
              [LED6].[Dimension Value Code] AS [PURCHASER],
              [LED7].[Dimension Value Code] AS [SALESCAMPAIGN],
              [LED8].[Dimension Value Code] AS [SALESPERSON],
            [LED9].[Dimension Value Code] AS [WAREHOUSE]
            FROM [dbo].[CRONUS EXT International Ltd_$Value Entry] [Value Entry]
              LEFT JOIN [dbo].[CRONUS EXT International Ltd_$Item] [Item] ON ([Value Entry].[Item No_] = [Item].[No_])
              LEFT JOIN [dbo].[CRONUS EXT International Ltd_$Ledger Entry Dimension] [LED1] ON ([Value Entry].[Entry No_] = [LED1].[Entry No_] AND [LED1].[Dimension Code] = 'AREA' AND
            [LED1].[Table ID] = 5802)
              LEFT JOIN [dbo].[CRONUS EXT International Ltd_$Ledger Entry Dimension] [LED2] ON ([Value Entry].[Entry No_] = [LED2].[Entry No_] AND [LED2].[Dimension Code] = 'BUSINESSGROUP' AND
            [LED2].[Table ID] = 5802)
              LEFT JOIN [dbo].[CRONUS EXT International Ltd_$Ledger Entry Dimension] [LED3] ON ([Value Entry].[Entry No_] = [LED3].[Entry No_] AND [LED3].[Dimension Code] = 'CUSTOMERGROUP' AND
            [LED3].[Table ID] = 5802)
              LEFT JOIN [dbo].[CRONUS EXT International Ltd_$Ledger Entry Dimension] [LED4] ON ([Value Entry].[Entry No_] = [LED4].[Entry No_] AND [LED4].[Dimension Code] = 'DEPARTMENT' AND
            [LED4].[Table ID] = 5802)
              LEFT JOIN [dbo].[CRONUS EXT International Ltd_$Ledger Entry Dimension] [LED5] ON ([Value Entry].[Entry No_] = [LED5].[Entry No_] AND [LED5].[Dimension Code] = 'PROJECT' AND
            [LED5].[Table ID] = 5802)
              LEFT JOIN [dbo].[CRONUS EXT International Ltd_$Ledger Entry Dimension] [LED6] ON ([Value Entry].[Entry No_] = [LED6].[Entry No_] AND [LED6].[Dimension Code] = 'PURCHASER' AND
            [LED6].[Table ID] = 5802)
              LEFT JOIN [dbo].[CRONUS EXT International Ltd_$Ledger Entry Dimension] [LED7] ON ([Value Entry].[Entry No_] = [LED7].[Entry No_] AND [LED7].[Dimension Code] = 'SALESCAMPAIGN' AND
            [LED7].[Table ID] = 5802)
              LEFT JOIN [dbo].[CRONUS EXT International Ltd_$Ledger Entry Dimension] [LED8] ON ([Value Entry].[Entry No_] = [LED8].[Entry No_] AND [LED8].[Dimension Code] = 'SALESPERSON' AND
            [LED8].[Table ID] = 5802)
              LEFT JOIN [dbo].[CRONUS EXT International Ltd_$Ledger Entry Dimension] [LED9] ON ([Value Entry].[Entry No_] = [LED9].[Entry No_] AND [LED9].[Dimension Code] = 'WAREHOUSE' AND
            [LED9].[Table ID] = 5802)

            ------- end of code -----

             

            You can notice here, that basically I have 9 left joins to the same table, as the conditions in the ON clause differ in order to add different records from the same table.

            Although it works pretty fast, I really wonder if this is a decent way of tackling this matter...