Converting span of years into list of distinctive years

[Rosohatica]

Hello all,

I am new to Alteryx and came across it while searching for an Excel solution. I think this is perfect software to solve my problem though,

So, I have an excel file which consists of river gauging station data. First column is a gauging station code, its just the name. Second column consists of spans of years during which the gauging has been working properly. Sometimes there are several spans in the cell separated by comma. And sometimes its a span and a single year. Anyway, the idea is to get a list of distinctive years instead of list of spans of years.There is around 1000 gauging stations I have to do this for. So doing it one by one is not an option, LOL.

Do you have an idea how?

I am attaching a screenshot  how the input table looks like and how I would like an output to look like.

Thanks.

  

[danilang · Accepted solution]

Hi @Rosohatica 

You can do it like this

Use a Text To Columns tool to split the ranges into individual rows and then a formula tool to get the start and end year for each range.  Add a Generate Rows tool to generate a one row from start to end.  The find the min and max year and generate a row for each year in the range.  Join this back to the original data and union the year that aren't in the input data set.  Add a value column since the crosstab tool requires that the headers and values come from different fields.  Crosstab and remove the null station rows

As far as the header that spans multiple columns, this something that you need the reporting tools for.  I'll show you how to add in the report formatting tomorrow unless someone else get there first

Dan

[randreag]

Hi @Rosohatica 

I attached an example of how you can do it

I hope it helps

[mot]

Hi @Rosohatica,

Please see the attached workflow. I hope this helps. Best.

[Rosohatica]

Hi mot, thanks for giving this an effort.

I was amazed as how fast you came up with it. Works perfectly with your example, but when I tried to populated it with more rows, an error happened. See it attached please.

 

 

[mot]

Hi @Rosohatica,

I would be happy to look into it but I have to replicate the error first. Could you share the excel file with the data in the screenshot? Thank you.

[Rosohatica]

Sure, thanks. See it attached.

[Rosohatica]

Aruiz thanks, I see now you sent a file as well. Your routine unfortunately also  gives me error if I try use it on the long list.

[Rosohatica]

Dan, I just noticed you attached the file as well, I tested it and I think it worked !!!

Thanks so much. It started to count years from 0 though but it was no problem to delete those first 1899 columns.

I would like to see if the other two guys can get their routines working as well. Its a good way for me to try learn the software - different paths that can all lead to the solution.

Looking forward to the rest. Best wishes.

[mot]

Hi @Rosohatica,

I was just able to look at it. There was an error since 2 codes did not have any years. Please see the attached updated workflow.

I hope this helps. Best.