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

split years from one cell to multiple cell

jasonsnare
Level 1
split years from one cell to multiple cell

HI,

I have one dataset and it contains the year ranges in one cell (1993-1996). I want to split each year into separate cells (1993,1994,1995,1996). Does anyone have an Idea suggest me. 

2 Replies
Rushil09
Level 2
Level 2

Hi, 

How exactly will the cells  be laid out? in form of columns?

Can you sen the screenshot of what you want to do if i am getting this wrong.

AlexGo
Dataiker
Dataiker

If you want to use the visual recipe steps and you have a consistent format, you could do something like:

  1. Split the cell on '-',
  2. Use a formula such as "forRange(year_range_0,year_range_1, 1+1, v, v)"
  3. Unnest the array into separate columns

Screen Shot 2022-04-12 at 9.31.25 AM.png

This gets the first and last year, takes all years between the first and the last, steps through each year and then applies a formula to it (in this case the formula is just returning the value).

You might have issues if the format is inconsistent though e.g. there's a single year. You would have to edit the formula step to include an 'if' to check for things like this.

 

0 Kudos