Discover this year's submissions to the Dataiku Frontrunner Awards and give kudos to your favorite use cases and success stories!READ MORE

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
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
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