7 Replies Latest reply on Nov 5, 2013 1:07 AM by muthu.krishnan

    Merge data from two columns

    Jonathan Richman

      Check out the attached sample of data (I had to take out product names to blind the data). I need to merge all the products (named item1, item2, etc.) together even though they’re in different columns and also somehow preserve the quantity. I need to be able to show how many of itemX were purchased, etc. Can’t find any documentation how to do this. I'm guessing there is some calculation I can put together, but I'm totally new to Tableau and none of the 50 things I've tried have worked.


      Basically, the idea would be to take all the product names from all those columns (there are 20 product columns) to make one single column and then to match the quantity of each item purchased from across all those columns matched to the right product.

       

      This data format is a side effect of going from XML to CSV apparently.

       

      Any help you can provide would be appreciated...including how to export from XML to make it a little simpler once I get it into Tableau.

       

      Thanks,

      JMR

        • 1. Re: Merge data from two columns
          Aaron Clancy

          Your data should be structured more transactionally.

          Screen Shot 2013-11-04 at 2.40.36 PM.png

          If you can get the export process to be more like what I have above it will be more usable in Tableau.

          Screen Shot 2013-11-04 at 2.46.20 PM.png

          • 2. Re: Merge data from two columns
            Ramon Martinez

            Hi Jonathan,

             

            Make a connection to your data source (text file, csv). In the Text Filke Connection Dialogue, click on Custom  SQL, instead of Single, then insert this SQL sentece

             

            SELECT [datasample#csv].[order no] AS [order no],
              [datasample#csv].[product 1] AS [product],
              [datasample#csv].[quantity 1] AS [quantity]
            FROM [datasample#csv]
            UNION
            SELECT [datasample#csv].[order no] AS [order no],
              [datasample#csv].[product 2] AS [product],
              [datasample#csv].[quantity 2] AS [quantity]
            FROM [datasample#csv]
            UNION
            SELECT [datasample#csv].[order no] AS [order no],
              [datasample#csv].[product 3] AS [product],
              [datasample#csv].[quantity 3] AS [quantity]
            FROM [datasample#csv]
            UNION
            SELECT [datasample#csv].[order no] AS [order no],
              [datasample#csv].[product 4] AS [product],
              [datasample#csv].[quantity 4] AS [quantity]
            FROM [datasample#csv]
            UNION
            SELECT [datasample#csv].[order no] AS [order no],
              [datasample#csv].[product 5] AS [product],
              [datasample#csv].[quantity 5] AS [quantity]
            FROM [datasample#csv]
            

             

            This SQL sentence transforms the structure of your data set in a way that you are able to your analysis.

             

            Take a look at the attached workbook.

             

            Best,

            Ramon

            • 3. Re: Merge data from two columns
              Jonathan Richman

              Thanks Ramon.

               

              I see where you're going here and this basically works except for two problems. The sample data I gave was massively simplified. There are something like 150 different items (with unique names, not "itemX" like in the sample), so I'd need to go through and catalogue all of these before I could even write it. Another issue is that your example excludes the order number like in Aaron Clancy's concept in the first answer. Not sure there's a simple script that can do it, but I'll see if I can come up with something.

              • 4. Re: Merge data from two columns
                Jonathan Richman

                Thanks Aaron.

                 

                I was afraid this was the answer, so I'll head back to the drawing board on my export and see if I can change around the script that pulled this together.

                • 5. Re: Merge data from two columns
                  Aaron Clancy

                  Can you provide an example of the XML structure?

                   

                  Sent from my iPhone

                  • 6. Re: Re: Merge data from two columns
                    Jonathan Richman

                    Here's what a single order might look like. Apologies in advance for the product names.

                     

                    I over-simplified my need just a bit, as I left out that each product could have a modifier as well. I'd love to capture this information appropriately and match it where it belongs, but beggars can't be choosers. In case it's hard to read here, I'll attach a file as well.

                     

                    Thanks for taking a look.

                     

                    <order>

                          <orderNumber>286121</orderNumber>

                          <storeName>Store#1</storeName>

                          <storeRef>123</storeRef>

                          <customer>

                            <custRef>1434001</custRef>

                            <firstName>Test</firstName>

                            <lastName>Order</lastName>

                            <emailAddress>me@me.com</emailAddress>

                          </customer>

                          <subtotal>25.2700</subtotal>

                          <discount>0.0000</discount>

                          <tax>0.0000</tax>

                          <total>25.2700</total>

                          <timePlaced>2012-08-21T15:57:21</timePlaced>

                          <timeClosed>2012-08-21T16:09:20.69</timeClosed>

                          <products>

                            <orderProduct name="My First Widget" quantity="1" baseCost="10.9900">

                              <modifiers>

                                <modifier name="Red" cost="0.0000" />

                              </modifiers>

                            </orderProduct>

                            <orderProduct name="Thing-ama-bob" quantity="1" baseCost="6.9900">

                              <modifiers>

                                <modifier name="Right-handed" cost="0.0000" />

                                <modifier name="Green" cost="0.0000" />

                              </modifiers>

                            </orderProduct>

                            <orderProduct name="Flying Pig" quantity="1" baseCost="7.2900">

                              <modifiers>

                                <modifier name="BBQ Sauce" cost="0.0000" />

                              </modifiers>

                            </orderProduct>

                          </products>

                        </order>

                    • 7. Re: Merge data from two columns
                      muthu.krishnan

                      Hi Jonathan,

                       

                      First you need to conver the xml to csv by using online converter. After that use the Ramon custom sql code.

                      It will work.

                       

                      Regards,

                      Muthu Krishnan. M