Question on Expanding Data to Display Unique Values

mmisky87
mmisky87 Member
in Magic ETL
aa.png

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

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can do this within a Magic ETL.

    If you do something like this:

    Screenshot 2024-01-31 at 4.29.23 PM.png

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can do this within a Magic ETL.

    If you do something like this:

    Screenshot 2024-01-31 at 4.29.23 PM.png

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • mmisky87
    mmisky87 Member

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

Categories