3 Replies Latest reply: Feb 1, 2018 4:55 AM by Anibal Martinez-Sistac RSS

    Where, Group by, and Concatenate sequentially in 2 tables

    Anibal Martinez-Sistac

      I have 2 simple American Football tables that I want to transform into one by sequentially implementing changes in both through "Data Load Editor". The 2 tables I have at the beginning are: games and seasons: The games table has the following look:

      games:

      Load * inline

      [

      id_game,game_type,         championship, price,   win,    profit, id_season, time, exchange

      1,         game,                   false,              1,         0,         1,         1,         5:02,     1.03

      2,         freegame,              false,              3,         1,         2,         1,         5:03,     1

      6,         freegame,              false,              2,         0,         2,         2,         5:05,     1

      3,         championship,       true,                4,         0,         4,         2,         5:04,     1.16

      4,         allstar,                   false,              2,         1,         1,         3,         5:06,     1.01

      5,         championship,       true,                7,         2,         5,         4,         5:07,     1.42

      7,         game,                   false,               1,         2,         -1,         5,         5:06,     0.75

      ];

      The seasons table has the following look:

      seasons:

      Load * inline

      [

      id_season, state

      1, florida

      2, georgia

      6, louissiana

      3, california

      4, arizona

      5, ny

      7, washington

      ];

      First step that I want to do is separate them in 2 different seasons types, one if it is a championship game (true) and another if it is not (false), and grouped by season with special interest metrics. I create a second temporal table called seasons_2 with this purpose (implementing joins):

      seasons_2:

      Load * inline

      [

      id_season, state_2

      1, florida

      2, georgia

      6, louissiana

      3, california

      4, arizona

      5, ny

      7, washington

      ];

       

      LEFT JOIN ([seasons])

      LOAD

      [id_season],

      Count(id_game)      As [Session #games pre championship true],

      sum(price)             As [Session price sum pre championship true],

      sum(profit)           As [Session profit sum pre championship true],

      '' as [Session #games pre championship false],

      '' as [Session price sum pre championship false],

      '' as [Session profit sum pre championship false]

      RESIDENT [games]

      where championship = 'true'

      GROUP BY [id_season];

       

      LEFT JOIN ([seasons_2])

      LOAD

      [id_season],

      '' as [Session #games pre championship true],

      '' as [Session price sum pre championship true],

      '' as [Session profit sum pre championship true],

      Count(id_game)      As [Session #games pre championship false],

      sum(price)             As [Session price sum pre championship false],

      sum(profit)           As [Session profit sum pre championship false]

      RESIDENT [games]

      where championship = 'false'

      GROUP BY [id_season];

       

      Finally i want table 2 under table 1 but keeping all fields. Here is where I fail, I have used this code but it is wrong:

      CONCATENATE (seasons)

      LOAD *

      RESIDENT seasons_2;

       

      What I would like is something as the following flow:

      Step 1: WHERE clause

      step 1.png

      Step 2: GROUP BY clause

      step 2.png

      Step 3: CONCATENATE and GROUP BY again

      step 3.png

      The final result must be equal to the table that you get after the last GROUP BY in the picture from above:

      result.png

      Thanks in advance for all help provided. Anibal

        • Re: Where, Group by, and Concatenate sequentially in 2 tables
          Sunny Talwar

          Try this

           

          games:

          LOAD * INLINE [

              id_game, game_type, championship, price, win, profit, id_season, time, exchange

              1, game, false, 1, 0, 1, 1, 5:02, 1.03

              2, freegame, false, 3, 1, 2, 1, 5:03, 1

              6, freegame, false, 2, 0, 2, 2, 5:05, 1

              3, championship, true, 4, 0, 4, 2, 5:04, 1.16

              4, allstar, false, 2, 1, 1, 3, 5:06, 1.01

              5, championship, true, 7, 2, 5, 4, 5:07, 1.42

              7, game, false, 1, 2, -1, 5, 5:06, 0.75

          ];


          Left Join (games)

          LOAD * INLINE [

              id_season, state

              1, florida

              2, georgia

              6, louissiana

              3, california

              4, arizona

              5, ny

              7, washington

          ];


          Temp:

          LOAD id_season,

          Sum(Sum_Price_False) as Sum_Price_False,

          Sum(Sum_Price_True) as Sum_Price_True,

          Sum(Sum_Profit_False) as Sum_Profit_False,

          Sum(Sum_Profit_True) as Sum_Profit_True,

          Sum(Count_False) as Count_False,

          Sum(Count_True) as Count_True

          Group By id_season;

          LOAD id_season,

          championship,

          Sum(If(championship = 'false', price)) as Sum_Price_False,

          Sum(If(championship = 'true', price)) as Sum_Price_True,

          Sum(If(championship = 'false', profit)) as Sum_Profit_False,

          Sum(If(championship = 'true', profit)) as Sum_Profit_True,

          Count(If(championship = 'false', id_season)) as Count_False,

          Count(If(championship = 'true', id_season)) as Count_True

          Resident games

          Group By id_season, championship;

           

          Capture.PNG

          • Re: Where, Group by, and Concatenate sequentially in 2 tables
            Anibal Martinez-Sistac

            Another possible answer, different from Sunny´s would be:

             

            games:

            Load * inline

            [

            id_game, game_type,            championship, price, win, profit, id_season, time,     exchange

            1,         game,                       false,         1,         0,         1,         1,         5:02,     1.03

            2,         freegame,                  false,         3,         1,         2,         1,         5:03,     1

            6,         freegame,                  false,         2,         0,         2,         2,         5:05,     1

            3,         championship,           true,          4,         0,         4,         2,         5:04,     1.16

            4,         allstar,                      false,         2,         1,         1,         3,         5:06,     1.01

            5,         championship,           true,          7,         2,         5,         4,         5:07,     1.42

            7,         game,                       false,         1,         2,         -1,        5,         5:06,     0.75

            ];

             

            seasons:

            Load * inline

            [

            id_season, state

            1, florida

            2, georgia

            6, louissiana

            3, california

            4, arizona

            5, ny

            7, washington

            ];

             

              round_championship_true:

              noconcatenate load

              [id_season],

              [id_game]                             as id_round_true,            

              [price]                                  as price_true,

              [profit]                                  as profit_true,

              [exchange]                           as rate_true,

              [time]                                   as time_true,

              [championship]                     as championship_true

              resident [games]

              where championship = 'true';

             

              round_championship_false:

              noconcatenate load

              [id_season],

              [id_game]                             as id_round_false,            

              [price]                                  as price_false,

              [profit]                                  as profit_false,

              [exchange]                           as rate_false,

              [time]                                  as time_false,

              [championship]                    as championship_false

              resident [games]

              where championship = 'false';

             

              round_championship_true_group_by:

              noconcatenate load

              [id_season],

              Count(id_round_true)                  as [Session #games pre true],

              ''                                               as [Session #games pre false],   

              sum(price_true)                         as [Session price sum pre true],

              ''                                              as [Session price sum pre false],

              sum(profit_true)                        as [Session profit sum pre true],

              ''                                             as [Session profit sum pre false],

              Max(rate_true)                         as [exchange_gb],

              Max(time_true)                        as [Session last time_gb]

              resident round_championship_true

              group by [id_season];

             

              round_championship_false_group_by:

              noconcatenate load

              [id_season],

              ''                                               as [Session #games pre true_gb],

              Count(id_round_false)                 as [Session #games pre false_gb],   

              ''                                               as [Session price sum pre true_gb],

              sum(price_false)                        as [Session price sum pre false_gb],

              ''                                               as [Session profit sum pre true_gb],

              sum(profit_false)                        as [Session profit sum pre false_gb],

              Max(rate_false)                         as [exchange_false_gb],

              Max(time_false)                        as [Session last time_false_gb]

              resident round_championship_false

              group by [id_season];

             

              CONCATENATE (round_championship_true_group_by)

              LOAD

              [id_season],

              [Session #games pre true_gb]                          as [Session #games pre true],

              [Session #games pre false_gb]                         as [Session #games pre false],

              [Session price sum pre true_gb]                       as [Session price sum pre true],

              [Session price sum pre false_gb]                      as [Session price sum pre false],

              [Session profit sum pre true_gb]                       as [Session profit sum pre true],

              [Session profit sum pre false_gb]                      as [Session profit sum pre false],

              [exchange_false_gb]                                        as [exchange_gb],

              [Session last time_false_gb]                             as [Session last time_gb]

              RESIDENT round_championship_false_group_by;

              drop table round_championship_false_group_by;

              drop table round_championship_true;

              drop table round_championship_false;

             

              championship_spins_table:

              noconcatenate LOAD

              [id_season],

              sum([Session price sum pre true])                   as [Sum price true],

              sum([Session price sum pre false])                  as [Sum price false],

              sum([Session profit sum pre true])                   as [Sum profit true],

              sum([Session profit sum pre false])                  as [Sum profit false],

              sum([Session #games pre true])                      as [Count true],

              sum([Session #games pre false])                     as [Count false]

              RESIDENT round_championship_true_group_by

              group by [id_season];

              drop table round_championship_true_group_by;

             

              Left Join ([seasons])

              Load

              *

              Resident championship_spins_table;

              drop table championship_spins_table;