fill the blanks of waterfall from other table

Question

I have two tables:

1. raw forecast data from forecast table, pulled by snap shot dates, and I use the data to created waterfall looks like this

item/snapshot        forecast weeks
123    | 8/25/14 | 9/1/14 | 9/8/14 | 9/15/14
--------------------------------------------
8/24/14|  7661   | 4980   | 588    | 2232
8/31/14|         | 8319   | 1968   | 2760
9/7/14 |         |        | 6931   | 684
9/14/14|         |        |        | 9328

---

Row labels are snapshot dates, and column labels are forecast weeks. Basically, there are bunch of snapshot dates of the data, and each snapshot will provide forecast data starting from this snapshot date, and it will show in forecast weeks. The first snapshot date x will have forecast weeks beyond x, second snapshot date y will only have forecast weeks beyond y.

---

Then I have table 2, consumption data from consumption table. I will use the week in consumption table to match the week in forecast table to insert consumptions to fill those blanks in waterfall.

If I'm doing it manually in excel, it would be forecast week 8/25, week number is 35, then I find week 35 from consumption table and insert here. So week 35 for all snapshot dates will be the same.

It will look like this:

item/snapshot        forecast weeks
123    | 8/25/14 | 9/1/14 | 9/8/14 | 9/15/14
--------------------------------------------
8/24/14|  7661   | 4980   | 588    | 2232
8/31/14|  2222   | 8319   | 1968   | 2760
9/7/14 |  2222   | 333    | 6931   | 684
9/14/14|  2222   | 333    | 444    | 9328

---

But the problem here is in my forecast table, for example, first snapshot date will have forecast week 1 to week 10, but second snapshot date will only have forecast week 2 to week 10. I don't know how and if it's possible to make this process automated bigquery sql, since the blanks basically means there is no data, no forecast weeks.

I would be really appreciated if someone can give me ideas

And here's my script:

 //Get item info from forecast table
DEFINE INLINE TABLE t1
SELECT CONCAT(SUBSTR(snapshot_date, -4, 4),'-',SUBSTR(snapshot_date, -10, 2),'-', SUBSTR(snapshot_date, -7, 2)) snapshot, 
item_name, 
type, 
item_description, 
CONCAT(SUBSTR(forecast_week_start_date, -4, 4),'-',SUBSTR(forecast_week_start_date, -10, 2),'-', SUBSTR(forecast_week_start_date, -7, 2)) forecast_week_start_date, 
SUM(quantity) qty, 
forecast_week_number, 
forecast_year_number,
CONCAT(STRING(forecast_year_number),'-',STRING(forecast_week_number) year_week
FROM forecast
WHERE 
concat(SUBSTR(snapshot_date, -4, 4),'-',SUBSTR(snapshot_date, -10, 2),'-', SUBSTR(snapshot_date, -7, 2)) >= 
strftime_usec(date_add(TIME_USEC_TO_WEEK(date_add(now(),-84 ,'DAY'),1),-1,'DAY'),'%Y-%m-%d')
GROUP BY snapshot, 
item_name, 
type, 
item_description, 
forecast_week_start_date, 
forecast_week_number, 
forecast_year_number,
year_week
ORDER BY sdm_week_start_date

//Get min year_week to use later
DEFINE INLINE TABLE t2
SELECT MIN(year_week) min_year_week
FROM t1


//Get consumption data and apply using dc deploy week
SELECT 
snapshot, 
item_name, 
type, 
item_description, 
forecast_week_start_date, 
qty,
forecast_week_number, 
forecast_year_number,
year_week
IF(t2.min_year_week!= year_week, qty+ABS(consumption_qty),qty)) quantity,

FROM t1
LEFT JOIN ALL 
 (SELECT item_name, week,sum(transaction_quantity) consumption_qt
  FROM consumption 
  GROUP BY item_name,week) inv
ON t1.year_week=inv.week AND t1.item_name=inv.item_name
CROSS JOIN t2

Answer 1

I took a crack at this.

These two queries produce tables with the values in your example. Suppose the output of the first query is written to consumption_table.

SELECT *
FROM
  (SELECT 123 AS item, '8/25/14' AS date, 2222 AS quantity),
  (SELECT 123 AS item, '9/1/14' AS date, 333 AS quantity),
  (SELECT 123 AS item, '9/8/14' AS date, 444 AS quantity),
  (SELECT 123 AS item, '9/15/14' AS date, 0 AS quantity);

And the output of the second query is written to forecast_table.

SELECT *
FROM
  (SELECT 123 AS item, '8/24/14' AS snapshot, '8/25/14' AS forecast, 7661 AS quantity),
  (SELECT 123 AS item, '8/24/14' AS snapshot, '9/1/14' AS forecast, 4980 AS quantity),
  (SELECT 123 AS item, '8/24/14' AS snapshot, '9/8/14' AS forecast, 588 AS quantity),
  (SELECT 123 AS item, '8/24/14' AS snapshot, '9/15/14' AS forecast, 2232 AS quantity),
  (SELECT 123 AS item, '8/31/14' AS snapshot, '9/1/14' AS forecast, 8319 AS quantity),
  (SELECT 123 AS item, '8/31/14' AS snapshot, '9/8/14' AS forecast, 1968 AS quantity),
  (SELECT 123 AS item, '8/31/14' AS snapshot, '9/15/14' AS forecast, 2760 AS quantity),
  (SELECT 123 AS item, '9/7/14' AS snapshot, '9/8/14' AS forecast, 6931 AS quantity),
  (SELECT 123 AS item, '9/7/14' AS snapshot, '9/15/14' AS forecast, 684 AS quantity),
  (SELECT 123 AS item, '9/14/14' AS snapshot, '9/15/14' AS forecast, 9328 AS quantity);

Then the following query produces something like what you want:

SELECT
    Consumed.item AS item,
    Consumed.snapshot AS snapshot,
    Consumed.date AS date,
    IF (Forecast.quantity IS NULL, Consumed.quantity, Forecast.quantity) AS quantity
FROM
    (SELECT
        C.item     AS item,
        S.snapshot AS snapshot,
        C.date     AS date,
        C.quantity AS quantity
     FROM
        (SELECT *
         FROM
            (SELECT '8/24/14' AS snapshot),
            (SELECT '8/31/14' AS snapshot),
            (SELECT '9/7/14' AS snapshot),
            (SELECT '9/14/14' AS snapshot)) AS S
     CROSS JOIN
        consumption_table AS C) AS Consumed
LEFT JOIN
    forecast_table AS Forecast
ON Consumed.item = Forecast.item AND 
   Consumed.snapshot = Forecast.snapshot AND
   Consumed.date = Forecast.forecast;

The key to this query is that the CROSS JOIN produces all of the desired output rows with the consumed quantity already in them. Then the LEFT JOIN preserves all of those rows and chooses the forecast quantity when it's available.