11 Replies Latest reply on Nov 29, 2017 10:50 AM by Tyler Garrett

    Line Chart with more than one time dimension

    Matteo Andolfi

      Hi All,

      I'm almost new to Tableau.

      I've to make a chart with different time dimension on the x-axis.

      I'll try to describe my problem:

       

      I have 2 parameter Date From and Date To,

      I have my filter whit this condition:

      ([0.date]>=[Date From] AND [0.date]<=[Date To]) OR ([0.date]>=DATEADD('month',-1,[Date From])  AND [0.date]<=DATEADD('month',-1,[Date To]))

       

      and a calculated field to get the different color:

       

      IF ([0.date]>=[Date From] AND [0.date]<=[Date To]) THEN 'CURRENT'

      ELSEIF ([0.date]>=DATEADD('month',-1,[Date From])  AND [0.date]<=DATEADD('month',-1,[Date To])) THEN 'LAST MONTH'

      END

      When I select the date range inside one month, it work like a charm: (see the below screenshot)

      Screenshot0.jpg

       

      but when I select more than one month on the parameters, the dashboard display as follow:

      Screenshot.jpg

      Because my current range overlap with the (range -1 month)

       

      I cannot use a Custom SQL because the dataset is huge with more than 1 hierarchical dimension and i have to use the tableau filter directly on the dataset.

       

      I cannot attach workbook because the data are sensible,

       

      could you give me some help?

       

      Thank you and have a nice day

       

      Bye

      Matteo

        • 1. Re: Line Chart with more than one time dimension
          Jim Dehner

          Hi

           

          I think the issue is with your if statement logic - your If statements check to values that are "This Month" or "last Month" based on a parameters

          The work with the 1 month scenario because a data is either in this month - last month or it is out of range

          When you try to extend to 2 months you have dates that you want to see in the current month interval and in the last month interval - but that can't happen - when the record is processed it returns a True when checked against the current interval and the process moves on to the next record -

           

          The record can not return a true against 2 separate clauses in the statement

           

          Jim

           

          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: Line Chart with more than one time dimension
            Matteo Andolfi

            Hi Jim and thanks for your reply.

            Yes, the problem is the if statement, but how i can achieve my target?

            do you know?

             

            thanks again

             

            Matteo

            • 3. Re: Line Chart with more than one time dimension
              Jim Dehner

              Please attach your workbook in a TWBX format

               

              Jim

              • 4. Re: Line Chart with more than one time dimension
                Tyler Garrett

                Are you familiar with a DIM_TIME table, from a EDW perspective, it's a best practice - as each dimensional or agg table will need a logical time table.

                 

                Especially a best practice if you have multiple dates that are needing to be 'overlapped'...

                 

                PART 1:

                 

                If the dates are not different

                Then JOIN on date... Date=Date... pretty much always and forever, which makes SQL so easy

                 

                If you need to join on DATE, you will need to aggregate to a DATE level prior to joining in Tableau. Oh snap.

                SQL:

                Select

                dim_stuff, measure, measure, dim_morestuff, DATE

                from table

                GROUP BY Day(date), dim_stuff, dim_morestuff

                not the most THE BEST sql code, but is code that is necessary to join your date on date, so I'm keeping it really basic.

                 

                If the dates are different

                 

                For big companies, they have multiple fiscal flags - based on their clients, or customer needs... Things get more complex and keys are necessary, so I'm going to keep it high level.

                 

                Multiple dates, that are subtly different, enables you to generate this DIM_Time table, wish EASE -> if you like excel and have a DBA

                 

                Each time has a means of syncing, no matter how it hits your data, everything can be synced in a table.

                 

                If you have two times, that need to be synced on ONE table. Then I would recommend simulating it first inside of EXCEL, then running your report off this sample data.

                 

                I like to draw this on a whiteboard - and I'm afraid we won't have that luxury, so I will go ahead and illustrate it on excel.

                 

                Screen Shot 2017-11-28 at 4.19.50 PM.png

                No matter the date, or the complexity, a simple table to explain your 'needs' - will offer your data architect or DBA - a simple explanation of your needs.

                 

                Also, you can materialize a TABLE, and forecast it out to 2020, and dump it to CSV, and import the CSV into your database, call it DIM_TIME, wahoo!

                 

                I would strongly advise against 'ETLing' your data on the front end, or expect super slow workbook in your future.

                 

                From a date perspective, this will always be a very frustrating/challenging process to 'master' in the product (and I've only met one person in my life that could come up with 'anything you want' on the front end), and I'm saying this with a backbone of data warehousing for companies like Goodwill, which had stores in different timezones, and different flags for different dates. And I feel strongly about mastering your dates in a relational table. And then depending on your needs, you can pull from the master DIM_TIME table.

                 

                Again, I've played this DATE judo game my entire business intelligence career - and if this isn't a nail on your solution, no worries, people search these 'phrases' - in the thousands per MONTH. And I'm looking to answer future searches on the forums/google, etc.

                 

                Just ignore this, I'm merely showing you that I've played some really wild Date games, and cleaning it up on the backend is much easier than playing this front-end judo... (hopefully this isn't the solution, but I've done 100's and 100's of whiteboard sessions working out awkward date things)

                IMG_5731.jpg

                If anything, I highly recommend you whiteboard it, and I promise the solution will pop out faster.

                Here's another example of figuring out wild date stuff.

                Screen Shot 2017-11-28 at 4.35.53 PM.png

                 

                Each time I hear 'different dates' and 'reporting' ---> I always ask for mapping documentation --> for the datebase.

                To determine if there is already a DIM_TIME table generated from 'previous solutions' and who knows - this might be done already. And that's great.

                 

                I know I'm speaking about this very high level, but that's as good as it gets when I don't have read access to your internal database. And throwing anything at you that isn't related to ETL prior to, may only be a wheel spin for you, unless you're an absolute JEDI in Tableau. Which *hats off and kowtow to you if you are.

                 

                Best,

                Tyler

                Dev3lop

                • 5. Re: Line Chart with more than one time dimension
                  Tyler Garrett

                  If you're doing a period over period (look at calc below). This would be the easiest path to where you're driving.

                   

                  And I've never heard anyone say SQL wasn't possible - unless they were a consultant and their client doesn't trust them running sql in their environment.

                   

                  That may be a bad deal because now the client is waiting on you to do something that can be easily fulfilled in SQL.

                   

                  Which from my perspective, is burning billable hours unnecessarily.

                   

                  You may want to bubble up to them that you need to do SQL to complete the request - without too much complex front end solutions, which won't be supportable by the customer unless you write verbose documentation around 'why and how.'

                   

                  There's a double edge blade here you're playing with, and it's the value add of a front end 'chunk of code' to make something easy to do - in SQL... And no, there's not DATA too big for SQL, that's not a thing mate. I would highly recommend SQL - or fdfghjkljhgfdghjkl;kjhgfdghjkl;kjhcg LOL

                   

                  Screen Shot 2017-11-28 at 4.42.18 PM.png

                   

                  Best,

                  Tyler

                  Dev3lop

                  • 6. Re: Line Chart with more than one time dimension
                    Tyler Garrett

                    Last but not least. You can do multiple lines per DATE, by simply SQLing measures into dimensional stacks. Again, SQL is the only path for this one. Especially if you're throwing in stacked bar charts with multiple lines, which is a BIG idea request - but a simple SQL solution.

                     

                    No data is too big FOR SQL, unless you're talking about IoT devices, which require instant read/write and that's not what we are talking about

                     

                    Here's a picture of some practice code I built this year, that shows how I stacked multiple lines, and stacked bars because the request was BEYOND Tableau Native features. And SQL ... IS ... mandatory. There's no alternative, except +1 the IDEA Forum.

                     

                    Screen Shot 2017-11-28 at 4.49.36 PM.png

                     

                     

                     

                    Best,

                    Tyler

                    Dev3lop

                    • 7. Re: Line Chart with more than one time dimension
                      Matteo Andolfi

                      Hi,

                      Thank you Tyler for all the explanation....

                      unfortunatelly my datasource is not a relational DB, it is Cloudera Hive (I forgot to mention...).

                      With this datasource, I cannot perform joins or other operations because there are 134 millions of records and the time that i've have to wait for the result is huge.

                      And also, the operation on date with Hive is not so simple, i have to use DATETIME to parse the date and RAWSQL to get the various date (i.e: date-1month, date-1year....)

                      I cannot attach directly a workbook because my customer's Tableau , is not licensed to make twbx

                       

                      by the way i attach a simple workbook created with the trial version of tableau 10.4

                       

                      I really appreciate your help!

                       

                      Thanks!

                      • 8. Re: Line Chart with more than one time dimension
                        Matteo Andolfi

                        Hi all,

                        I've some news:

                        I've Used The Data Blending functionality, 3 times on the same dataset, (LastWeek, LastMonth, LastYear)

                        Like this post: Re: Help needed with Date calculations

                         

                        One thing that i don't like it is the following:

                        The query made by Tableau when DateFrom=2016-01-01 and DateTo=2017-08-31 is like:

                         

                        [...]FROM `schema`.`table`

                        WHERE (((`table`.`filter1` = 'XXX')

                                AND ((`table`.`filter2` = 'XXX')

                                AND (`table`.`filter3` = 'XXX')))

                                AND (((`table`.`filter4` = 'XXX')

                                AND (`table`.`filter5` = 'XXX'))

                                AND ((`table`.`filter6` = 'XXX')

                                AND ( (CASE

                            WHEN (1 IS NULL)

                                OR (10 IS NULL) THEN

                            NULL

                            WHEN 10 < 1 THEN

                            ''

                            WHEN 1 < 1 THEN

                            SUBSTRING(CAST((CAST(`table`.`day` AS TIMESTAMP) + interval 1 month) AS STRING),CAST(1 AS INT),CAST(10 AS INT))

                            ELSE SUBSTRING(CAST((CAST(`table`.`day` AS TIMESTAMP) + interval 1 month) AS STRING),CAST(1 AS INT),CAST(10 AS INT)) END) IN ('2016-01-01', '2016-01-02', '2016-01-03',

                            '2016-01-04', '2016-01-05', '2016-01-06', '2016-01-07', '2016-01-08', '2016-01-09', '2016-01-10', '2016-01-11', '2016-01-12', '2016-01-13', '2016-01-14', '2016-01-15', '2016-01-16',

                            '2016-01-17', '2016-01-18', '2016-01-19', '2016-01-20', '2016-01-21', '2016-01-22', '2016-01-23', '2016-01-24', '2016-01-25', '2016-01-26', '2016-01-27', '2016-01-28', '2016-01-29',

                            '2016-01-30', '2016-01-31', '2016-02-01', '2016-02-02', '2016-02-03', '2016-02-04', '2016-02-05', '2016-02-06', '2016-02-07', '2016-02-08', '2016-02-09', '2016-02-10', '2016-02-11',

                            '2016-02-12', '2016-02-13', '2016-02-14', '2016-02-15', '2016-02-16', '2016-02-17', '2016-02-18', '2016-02-19', '2016-02-20', '2016-02-21', '2016-02-22', '2016-02-23', '2016-02-24',

                            '2016-02-25', '2016-02-26', '2016-02-27', '2016-02-28', '2016-02-29', '2016-03-01', '2016-03-02', '2016-03-03', '2016-03-04', '2016-03-05', '2016-03-06', '2016-03-07', '2016-03-08',

                            '2016-03-09', '2016-03-10', '2016-03-11', '2016-03-12', '2016-03-13', '2016-03-14', '2016-03-15', '2016-03-16', '2016-03-17', '2016-03-18', '2016-03-19', '2016-03-20', '2016-03-21',

                            '2016-03-22', '2016-03-23', '2016-03-24', '2016-03-25', '2016-03-26', '2016-03-27', '2016-03-28', '2016-03-29', '2016-04-01', '2016-04-02', '2016-04-03', '2016-04-04', '2016-04-05',

                            '2016-04-06', '2016-04-07', '2016-04-08', '2016-04-09', '2016-04-10', '2016-04-11', '2016-04-12', '2016-04-13', '2016-04-14', '2016-04-15', '2016-04-16', '2016-04-17', '2016-04-18',

                            '2016-04-19', '2016-04-20', '2016-04-21', '2016-04-22', '2016-04-23', '2016-04-24', '2016-04-25', '2016-04-26', '2016-04-27', '2016-04-28', '2016-04-29', '2016-04-30', '2016-05-01',

                            '2016-05-02', '2016-05-03', '2016-05-04', '2016-05-05', '2016-05-06', '2016-05-07', '2016-05-08', '2016-05-09', '2016-05-10', '2016-05-11', '2016-05-12', '2016-05-13', '2016-05-14',

                            '2016-05-15', '2016-05-16', '2016-05-17', '2016-05-18', '2016-05-19', '2016-05-20', '2016-05-21', '2016-05-22', '2016-05-23', '2016-05-24', '2016-05-25', '2016-05-26', '2016-05-27',

                            '2016-05-28', '2016-05-29', '2016-05-30', '2016-06-01', '2016-06-02', '2016-06-03', '2016-06-04', '2016-06-05', '2016-06-06', '2016-06-07', '2016-06-08', '2016-06-09', '2016-06-10',

                            '2016-06-11', '2016-06-12', '2016-06-13', '2016-06-14', '2016-06-15', '2016-06-16', '2016-06-17', '2016-06-18', '2016-06-19', '2016-06-20', '2016-06-21', '2016-06-22', '2016-06-23',

                            '2016-06-24', '2016-06-25', '2016-06-26', '2016-06-27', '2016-06-28', '2016-06-29', '2016-06-30', '2016-07-01', '2016-07-02', '2016-07-03', '2016-07-04', '2016-07-05', '2016-07-06',

                            '2016-07-07', '2016-07-08', '2016-07-09', '2016-07-10', '2016-07-11', '2016-07-12', '2016-07-13', '2016-07-14', '2016-07-15', '2016-07-16', '2016-07-17', '2016-07-18', '2016-07-19',

                            '2016-07-20', '2016-07-21', '2016-07-22', '2016-07-23', '2016-07-24', '2016-07-25', '2016-07-26', '2016-07-27', '2016-07-28', '2016-07-29', '2016-07-30', '2016-08-01', '2016-08-02',

                            '2016-08-03', '2016-08-04', '2016-08-05', '2016-08-06', '2016-08-07', '2016-08-08', '2016-08-09', '2016-08-10', '2016-08-11', '2016-08-12', '2016-08-13', '2016-08-14', '2016-08-15',

                            '2016-08-16', '2016-08-17', '2016-08-18', '2016-08-19', '2016-08-20', '2016-08-21', '2016-08-22', '2016-08-23', '2016-08-24', '2016-08-25', '2016-08-26', '2016-08-27', '2016-08-28',

                            '2016-08-29', '2016-08-30', '2016-08-31', '2016-09-01', '2016-09-02', '2016-09-03', '2016-09-04', '2016-09-05', '2016-09-06', '2016-09-07', '2016-09-08', '2016-09-09', '2016-09-10',

                            '2016-09-11', '2016-09-12', '2016-09-13', '2016-09-14', '2016-09-15', '2016-09-16', '2016-09-17', '2016-09-18', '2016-09-19', '2016-09-20', '2016-09-21', '2016-09-22', '2016-09-23',

                            '2016-09-24', '2016-09-25', '2016-09-26', '2016-09-27', '2016-09-28', '2016-09-29', '2016-09-30', '2016-10-01', '2016-10-02', '2016-10-03', '2016-10-04', '2016-10-05', '2016-10-06',

                            '2016-10-07', '2016-10-08', '2016-10-09', '2016-10-10', '2016-10-11', '2016-10-12', '2016-10-13', '2016-10-14', '2016-10-15', '2016-10-16', '2016-10-17', '2016-10-18', '2016-10-19',

                            '2016-10-20', '2016-10-21', '2016-10-22', '2016-10-23', '2016-10-24', '2016-10-25', '2016-10-26', '2016-10-27', '2016-10-28', '2016-10-29', '2016-10-30', '2016-11-01', '2016-11-02',

                            '2016-11-03', '2016-11-04', '2016-11-05', '2016-11-06', '2016-11-07', '2016-11-08', '2016-11-09', '2016-11-10', '2016-11-11', '2016-11-12', '2016-11-13', '2016-11-14', '2016-11-15',

                            '2016-11-16', '2016-11-17', '2016-11-18', '2016-11-19', '2016-11-20', '2016-11-21', '2016-11-22', '2016-11-23', '2016-11-24', '2016-11-25', '2016-11-26', '2016-11-27', '2016-11-28',

                            '2016-11-29', '2016-11-30', '2016-12-01', '2016-12-02', '2016-12-03', '2016-12-04', '2016-12-05', '2016-12-06', '2016-12-07', '2016-12-08', '2016-12-09', '2016-12-10', '2016-12-11',

                            '2016-12-12', '2016-12-13', '2016-12-14', '2016-12-15', '2016-12-16', '2016-12-17', '2016-12-18', '2016-12-19', '2016-12-20', '2016-12-21', '2016-12-22', '2016-12-23', '2016-12-24',

                            '2016-12-25', '2016-12-26', '2016-12-27', '2016-12-28', '2016-12-29', '2016-12-30', '2017-01-01', '2017-01-02', '2017-01-03', '2017-01-04', '2017-01-05', '2017-01-06', '2017-01-07',

                            '2017-01-08', '2017-01-09', '2017-01-10', '2017-01-11', '2017-01-12', '2017-01-13', '2017-01-14', '2017-01-15', '2017-01-16', '2017-01-17', '2017-01-18', '2017-01-19', '2017-01-20',

                            '2017-01-21', '2017-01-22', '2017-01-23', '2017-01-24', '2017-01-25', '2017-01-26', '2017-01-27', '2017-01-28', '2017-01-29', '2017-01-30', '2017-01-31', '2017-02-01', '2017-02-02',

                            '2017-02-03', '2017-02-04', '2017-02-05', '2017-02-06', '2017-02-07', '2017-02-08', '2017-02-09', '2017-02-10', '2017-02-11', '2017-02-12', '2017-02-13', '2017-02-14', '2017-02-15',

                           

                        [...]

                         

                         

                        So many day! why Tableau doesn't use a BETWEEN? :O

                         

                        Any helps?

                         

                        Thanks in advance!

                        Matteo

                        • 9. Re: Line Chart with more than one time dimension
                          Andrej Schmelzer

                          What is the exact requirement your customer has?

                           

                          Your issue is that your If/Elseif statement can only return either "current" or "previous month". For more months then 1, there will be dataparts which have are both.

                           

                          I am assuming that your customer is mostly interested in having the current sales/whatever as well as the difference in sales/whatever to the previous month.

                           

                          This is likely a lot easier to do with a lookup table calc.

                          • 10. Re: Line Chart with more than one time dimension
                            Tyler Garrett

                            Querying 134 million records doesn't take that long on an in memory database unless the joins are incorrect.

                             

                            Good news. And bad news. Regrettably - You might be in over your head, contact your senior architect or principal architect (hopefully you're not freelance) - do you have a data architect to help you? Are you internal? You really need to bubble this up to the senior DBA or Hive admin.

                             

                             

                            Join's appear to be possible: here's a PASTE directly from the website.

                             

                            HiveQL Select Joins

                            JOIN is a clause that is used for combining specific fields from two tables by using values common to each one. It is used to combine records from two or more tables in the database. It is more or less similar to SQL JOIN.

                            Syntax

                            join_table:  table_reference JOIN table_factor [join_condition] | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition | table_reference LEFT SEMI JOIN table_reference join_condition | table_reference CROSS JOIN table_reference [join_condition]

                            Example

                            We will use the following two tables in this chapter. Consider the following table named CUSTOMERS..

                            +----+----------+-----+-----------+----------+ | ID | NAME     | AGE | ADDRESS   | SALARY   | +----+----------+-----+-----------+----------+ | 1  | Ramesh   | 32  | Ahmedabad | 2000.00  | | 2  | Khilan   | 25  | Delhi     | 1500.00  | | 3  | kaushik  | 23  | Kota      | 2000.00  | | 4  | Chaitali | 25  | Mumbai    | 6500.00  | | 5  | Hardik   | 27  | Bhopal    | 8500.00  | | 6  | Komal    | 22  | MP        | 4500.00  | | 7  | Muffy    | 24  | Indore    | 10000.00 | +----+----------+-----+-----------+----------+ 

                            Consider another table ORDERS as follows:

                            +-----+---------------------+-------------+--------+ |OID  | DATE                | CUSTOMER_ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102 | 2009-10-08 00:00:00 |           3 | 3000   | | 100 | 2009-10-08 00:00:00 |           3 | 1500   | | 101 | 2009-11-20 00:00:00 |           2 | 1560   | | 103 | 2008-05-20 00:00:00 |           4 | 2060   | +-----+---------------------+-------------+--------+ 

                            There are different types of joins given as follows:

                            • JOIN
                            • LEFT OUTER JOIN
                            • RIGHT OUTER JOIN
                            • FULL OUTER JOIN

                            JOIN

                            JOIN clause is used to combine and retrieve the records from multiple tables. JOIN is same as OUTER JOIN in SQL. A JOIN condition is to be raised using the primary keys and foreign keys of the tables.

                            The following query executes JOIN on the CUSTOMER and ORDER tables, and retrieves the records:

                            hive> SELECT c.ID, c.NAME, c.AGE, o.AMOUNT FROM CUSTOMERS c JOIN ORDERS o ON (c.ID = o.CUSTOMER_ID); 

                            On successful execution of the query, you get to see the following response:

                            +----+----------+-----+--------+ | ID | NAME     | AGE | AMOUNT | +----+----------+-----+--------+ | 3  | kaushik  | 23  | 3000   | | 3  | kaushik  | 23  | 1500   | | 2  | Khilan   | 25  | 1560   | | 4  | Chaitali | 25  | 2060   | +----+----------+-----+--------+ 

                            LEFT OUTER JOIN

                            The HiveQL LEFT OUTER JOIN returns all the rows from the left table, even if there are no matches in the right table. This means, if the ON clause matches 0 (zero) records in the right table, the JOIN still returns a row in the result, but with NULL in each column from the right table.

                            A LEFT JOIN returns all the values from the left table, plus the matched values from the right table, or NULL in case of no matching JOIN predicate.

                            The following query demonstrates LEFT OUTER JOIN between CUSTOMER and ORDER tables:

                            hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE FROM CUSTOMERS c LEFT OUTER JOIN ORDERS o ON (c.ID = o.CUSTOMER_ID); 

                            On successful execution of the query, you get to see the following response:

                            +----+----------+--------+---------------------+ | ID | NAME     | AMOUNT | DATE                | +----+----------+--------+---------------------+ | 1  | Ramesh   | NULL   | NULL                | | 2  | Khilan   | 1560   | 2009-11-20 00:00:00 | | 3  | kaushik  | 3000   | 2009-10-08 00:00:00 | | 3  | kaushik  | 1500   | 2009-10-08 00:00:00 | | 4  | Chaitali | 2060   | 2008-05-20 00:00:00 | | 5  | Hardik   | NULL   | NULL                | | 6  | Komal    | NULL   | NULL                | | 7  | Muffy    | NULL   | NULL                | +----+----------+--------+---------------------+ 

                            RIGHT OUTER JOIN

                            The HiveQL RIGHT OUTER JOIN returns all the rows from the right table, even if there are no matches in the left table. If the ON clause matches 0 (zero) records in the left table, the JOIN still returns a row in the result, but with NULL in each column from the left table.

                            A RIGHT JOIN returns all the values from the right table, plus the matched values from the left table, or NULL in case of no matching join predicate.

                            The following query demonstrates RIGHT OUTER JOIN between the CUSTOMER and ORDER tables.

                            notranslate"> hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE FROM CUSTOMERS c RIGHT OUTER JOIN ORDERS o ON (c.ID = o.CUSTOMER_ID);

                            On successful execution of the query, you get to see the following response:

                            +------+----------+--------+---------------------+ | ID   | NAME     | AMOUNT | DATE                | +------+----------+--------+---------------------+ | 3    | kaushik  | 3000   | 2009-10-08 00:00:00 | | 3    | kaushik  | 1500   | 2009-10-08 00:00:00 | | 2    | Khilan   | 1560   | 2009-11-20 00:00:00 | | 4    | Chaitali | 2060   | 2008-05-20 00:00:00 | +------+----------+--------+---------------------+ 

                            FULL OUTER JOIN

                            The HiveQL FULL OUTER JOIN combines the records of both the left and the right outer tables that fulfil the JOIN condition. The joined table contains either all the records from both the tables, or fills in NULL values for missing matches on either side.

                            The following query demonstrates FULL OUTER JOIN between CUSTOMER and ORDER tables:

                            hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE FROM CUSTOMERS c FULL OUTER JOIN ORDERS o ON (c.ID = o.CUSTOMER_ID); 

                            On successful execution of the query, you get to see the following response:

                            +------+----------+--------+---------------------+ | ID   | NAME     | AMOUNT | DATE                | +------+----------+--------+---------------------+ | 1    | Ramesh   | NULL   | NULL                | | 2    | Khilan   | 1560   | 2009-11-20 00:00:00 | | 3    | kaushik  | 3000   | 2009-10-08 00:00:00 | | 3    | kaushik  | 1500   | 2009-10-08 00:00:00 | | 4    | Chaitali | 2060   | 2008-05-20 00:00:00 | | 5    | Hardik   | NULL   | NULL                | | 6    | Komal    | NULL   | NULL                | | 7    | Muffy    | NULL   | NULL                | | 3    | kaushik  | 3000   | 2009-10-08 00:00:00 | | 3    | kaushik  | 1500   | 2009-10-08 00:00:00 | | 2    | Khilan   | 1560   | 2009-11-20 00:00:00 | | 4    | Chaitali | 2060   | 2008-05-20 00:00:00 | +------+----------+--------+---------------------+

                             

                            Best,

                            Tyler

                            • 11. Re: Line Chart with more than one time dimension
                              Tyler Garrett

                              Lastly,

                              Prerequisites

                              Before proceeding with this tutorial, you need a basic knowledge of Core Java, Database concepts of SQL, Hadoop File system, and any of Linux operating system flavors.