Ventilate value year to all months (hive SQL)

Solved!
Hazou
Level 1
Ventilate value year to all months (hive SQL)
-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. 

0 Kudos
1 Solution
fchataigner2
Dataiker

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

View solution in original post

0 Kudos
1 Reply
fchataigner2
Dataiker

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

0 Kudos