Question on Expanding Data to Display Unique Values

mmisky87

Is there a way to take the data that has a start/stop year (Column A/B) and have it expand while retaining the associated notes from Column C? The data is in Domo, but can manipulate in excel. The goal is to convert columns A-C to E-F. Not sure which platform makes most sense to make this happen (excel, access, Domo).

GrantSmith

You can do this within a Magic ETL.

If you do something like this:

This will do a cartesian join between the list of years and your dataset and then filter the records where the year is in between your original start and end years. Add formula is adding a new column called Join Column with a value of 1 for the join.

mmisky87.txt

I've attached a JSON file with the definition (minus the input and output datasets) so you can copy the contents and then control+v/command+v/paste them into your Magic ETL canvas.

GrantSmith

You can do this within a Magic ETL.

If you do something like this:

This will do a cartesian join between the list of years and your dataset and then filter the records where the year is in between your original start and end years. Add formula is adding a new column called Join Column with a value of 1 for the join.

mmisky87.txt

I've attached a JSON file with the definition (minus the input and output datasets) so you can copy the contents and then control+v/command+v/paste them into your Magic ETL canvas.

mmisky87

Thank you Grant for the response. I will be working on this.