3 Replies Latest reply on Jan 15, 2019 9:16 AM by Jim Dehner

    Union Weekly Report (diff countries/similar format in excel)

    Raymond Tam

      Hi there, need some help out of here.


      Background info:


      Every week I need to refresh my database with weekly sales data from 3 countries. The sales data is in excel with more or less similar structure.


      For now I create a master excel file by putting each week's sales data into new sheets (so every week 3 new sheets added). Then I union all of those sheets by Tableau Desktop. After that I can play around the number & keep track with product performance week by week, country by country etc. etc.


      After a while I start to put back previous weekly sale data & union them. Now I have maximum like 2 years data ie. 3 (countries) x 52 (weeks) x 2 (years) = 312 sheets! For sure it keeps growing.


      Then I observe slower response each time I add new union & sometimes I get error message from Tableau. I feel the way how I do can't stay forever so I need some wise solution to help me solve this issue.


      Would Tableau Prep be a solution & how?


      Should I output the big chunk of union(ed) data then start a new union to join new data?


      I have attached a master file (simplified) to illustrate the current situation. There are just 2 weeks from 3 countries with total weekly sales qty & qty by stores.


      Many thx for reading this & helping out. Do let me know if I need to provide more info.



        • 1. Re: Union Weekly Report (diff countries/similar format in excel)
          Jim Dehner

          Good morning

          I looked at the data if I understand the individual data sets are 1 week of store level sales - I can't discern the codes but it looks like the sales are at the product store level and results in a wide short table -

          Tableau and all other database based systems like lean tall files - so what to do

          can Prep help - Yes - you can start by pivoting the individual files around store and product so you have something that looks like a tall data set date - store - product - sales (not certain what else is in the table)  You may need to do 2 level pivoting in Prep  one for store and a second for product (note you can not do 2 level in Tableau proper - just Prep)


          then you can union each of those weekly files together - (I can't tell you if unio then pivot would be better because you noted that the files a more or less the same structure)


          That will result in a set that is more efficient



          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: Union Weekly Report (diff countries/similar format in excel)
            Raymond Tam

            Hi Jim, thx for your insight.


            I heard that Prep cannot union more than 10 tables. Should I use Prep everytime as a single flow then manually combine the data into excel?



            • 3. Re: Union Weekly Report (diff countries/similar format in excel)
              Jim Dehner

              you heard almost correct - there is a 10 file limit to any one union but then you can take the resulting "unioned files" and union them and so on -