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

GregC

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

GrantSmith

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.

GrantSmith

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.

GregC

Awesome! Worked perfectly. Thank you, Grant.