Where, Group by, and Concatenate sequentially in 2 tables

[Anonymous]

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 2: GROUP BY clause

Step 3: CONCATENATE and GROUP BY again

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

Thanks in advance for all help provided. Anibal

[sunny_talwar · Accepted solution]

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;

[Anonymous]

Hi Sunny,

Thnak you for your efficient response. I managed to do it in a not-so-efficient way creating several tables (have added another alternative to this thread). Nevertheless, I will mark your solution as the correct one, as it is much more efficient.

Thanks in advance for your help!

Anibal

[Anonymous]

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;