How do I create a matrix from two unique lists in magic ETL?

GregC
GregC Member
in Magic ETL

How do I create a matrix as shown in the "Result" image based on the two input lists provided (zeros filled in the field)? I am trying to do this in Magic ETL.

Thanks in advance!

Gregory

Input 1.png
Input 2.png
Result.png
Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Use a formula tile to add a constant called Join Column and set the value to one. Do this for both of your input datasets. Then use a join tile to join them on this new join column.

    After that, you can feed it into a dynamic unpivot tile to pivot your data on the date field to get the output format you're looking for. Alternatively you can use the output from above in a Pivot Table card to do this step.

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

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Use a formula tile to add a constant called Join Column and set the value to one. Do this for both of your input datasets. Then use a join tile to join them on this new join column.

    After that, you can feed it into a dynamic unpivot tile to pivot your data on the date field to get the output format you're looking for. Alternatively you can use the output from above in a Pivot Table card to do this step.

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

    Awesome! Worked perfectly. Thank you, Grant.

Categories