1 2 Previous Next 16 Replies Latest reply on Apr 13, 2017 4:56 PM by Debasish PaiMazumder

    Multidimensional array

    Debasish PaiMazumder

      Hi

      I am quite new in Tableau. I am wondering how does tableau handle multidimensional array.

      I have an array with dimension US county X time (daily data for three month = 92). I am preparing a *.csv file so that tableau can read the file.

      What would be the best format of the file -

      lat lon county time1 time2 time3..........time92

       

      or

      time lat lon county value

      time1

      time1

      :

      :

      time2

      time2

      :

      :

      time92

       

      with regards

      -Deb

        • 1. Re: Multidimensional array
          Luciano Vasconcelos

          It would be better if this array were only one field with separators in order to Tableau pivot your data.

          • 2. Re: Multidimensional array
            Kaz Shakir

            Debasish,

            The general rule with data in databases is that "Tall & Thin" is a better format than "Short & Wide."  That's not always the case.  In your exapmple, I would go with your second approach where each row represnts a unique combination of time and country.  You could also remove the lattitude and longitude values from this table (so that you just have Time, Country, Value), and create a second table that just has Country, Lat, Lon in it, and then join the two tables in Tableau on the Country field.  Lastly, depending on how you want your report to look in Tableau, you might need a "Calendar" table, where the columns would be Date, Day, Month, Quarter, Year - where there would be a row for each day of every year you have in your data, and you would categorize each day as to which month it belongs to, which quarter it belongs to and which year it belongs to - this makes it easier to do roll-ups based on these different categories.  And then you would join this calendar table to your main table where the Time columne would equal the Date column.

             

            Hope that's helpful.

            Kaz.

            • 3. Re: Multidimensional array
              Debasish PaiMazumder

              Thanks for your responses.  Can anyone point me to similar type of example ?

              with regards

              -Deb

              • 4. Re: Multidimensional array
                Kaz Shakir

                I can't point you to examples in these forums - although I know there are many.  You should probably study data noramlization to better understand the best ways to structure your data - you can google it, and here's one site I found that's pretty good: http://www.guru99.com/database-normalization.html . I'm not endorsing it, but it gives a good introduction to the topic.

                Kaz.

                • 5. Re: Multidimensional array
                  Debasish PaiMazumder

                  Thanks for your response.

                  -Deb

                  • 6. Re: Multidimensional array
                    Luciano Vasconcelos

                    Debasish, i made a mistake on previous answer. Sorry about that.

                    You can't split and pivot data at once.

                    I'll build an example for you.

                    • 7. Re: Multidimensional array
                      Luciano Vasconcelos

                      Hi.

                      I built an example using this data:

                       

                        

                      Team IDProcess ID201701201702201703
                      1110710775
                      12100121103
                      23897597
                      2411511884
                      25107111114
                      33103106114
                      317681103
                      369779105
                      371188594
                      3877122110
                      391097684
                      31012186104

                       

                      It works for excel files. I don't remember what kind of data supports be pivotted. Maybe you need to use Union in customSQL to reproduce this idea.

                      See steps below:

                       

                       

                      • 8. Re: Multidimensional array
                        Debasish PaiMazumder

                        Thanks Luciano.

                        -Deb

                        • 9. Re: Multidimensional array
                          Debasish PaiMazumder

                          Luciano Vasconcelos

                          Thanks for your response. It works for one variable. Now I have following file with two variables

                          lat lon county x(time1) x(time2) x(time3)..........x(time92) y(time1) y(time2) y(time3)..........y(time92)

                           

                          and I would like to create a following file

                           

                          time lat lon county x  y

                          time1

                          time1

                          :

                          :

                          time2

                          time2

                          :

                          :

                          time92

                           

                          when I applied pivot, I create one column for both x and y. how do I create two separate column for x and y?

                          Any solution ?

                           

                          -Deb

                          • 10. Re: Multidimensional array
                            Kaz Shakir

                            Deb,

                            From what you are describing, your data looks something like this:

                            (numbers are just random placeholders)

                             

                            Also, in your original post, you said that you are actually building the .csv files that you will be feeding in to Tableau.  If that's the case, then you should have a great deal of control over how to re-shape this data to make it more functional in Tableau.  If I were you, I would use Excel to help you re-shape the data.  Here are the steps that I would take.

                             

                            First, break the above data into three tables - (a) County - just has lat, lon, county, and countyID, (b) x - that has countyID, and related time columns, and (c) y - that has countyID, and related time columns: (note I just created the CountyID column - it's just sequential values)

                            County Table

                            x table

                            y table

                             

                            Next, you need to "un-roll" or "un-pivot" the x and y tables.  There is functionality in Excel that will allow you to do this.  Rather than try to explain it here - check out this video with some instructions about it: https://youtu.be/pUXJLzqlEPk

                             

                            After you "un-roll" those two tables, they should look like this (and I renamed them to x times, and y times)

                            x times table

                             

                            y times table

                             

                            The three tables that are most important and the County table, the x times table, and the y times table.  You can bring these tables together in Tableau, and ask Tableau to join them, like this:

                             

                            And your data pane in Tableau will now look like this:

                             

                            And you can see that the x times and values are broken out separately from the y times and values.

                             

                            Hope that helps.

                            Kaz.

                            • 11. Re: Multidimensional array
                              Luciano Vasconcelos

                              Post some data please.

                              • 12. Re: Multidimensional array
                                Debasish PaiMazumder

                                Kaz Shakir

                                I have tried that but excel unable pivot all the data.......giving me a message - "not all the source record has been copied"

                                -Deb

                                • 13. Re: Multidimensional array
                                  Debasish PaiMazumder

                                  Luciano Vasconcelos

                                  Here my file. In 1st row date repeat twice. for x1 (20170801-20170831) and then for x2(20170801-20170831). I would like to create data like

                                   

                                  city    city_ascii    state_id    state name    county name    date    x1    x2  

                                   

                                  with regards

                                  -Deb

                                  • 14. Re: Multidimensional array
                                    Kaz Shakir

                                    Deb,

                                    Looks like you maxed out Excel - LOL - you don't have to worry about that with Tableau.  You can break the data up into two parts in Excel, and then recombine it in Tableau using union.  Here's my shot at it.

                                     

                                    I broke up the data at row 20,000 - so, just like the previous example, I have a County table, but this time I have County_a and County_b, that look like this:

                                    County_a

                                    County_b

                                    And then, same for x1 and x2, there's x1_a and x1_b, and x2_a and x2_b:

                                    x1_a

                                     

                                    x1_b

                                     

                                    And then in Tableau, using the Union functionality added in version 10:

                                    County:

                                     

                                    And do the same for x1 and x2, and then join all the union-ed tables:

                                     

                                    And you get the same result:

                                    Kaz.

                                    1 2 Previous Next