Ventilate value year to all months (hive SQL)

Hazou
Hazou Registered Posts: 7 ✭✭✭✭
-1
Hello,
I have this table
Country Year Value
US20201200
GE20202400

I want to ventilate the value of the year over all the months (value year / 12)

Country Year Month Value
US20201100
US20202100
............
GE20201200

Thanks.

Best Answer

  • fchataigner2
    fchataigner2 Dataiker Posts: 355 Dataiker
    Answer ✓

    you can make a small dataset with 1 row per month, a cartesian join with that dataset to your table, and compute value * fraction.

    The dataset can be a simple editable dataset like:

    Screenshot 2021-04-27 at 05.27.02.png

    The join can compute the dispatched value with a post-join computed column like

    Screenshot 2021-04-27 at 05.27.14.png

Setup Info
    Tags
      Help me…