How to use value from beast mode as a value for all?

kklinger
kklinger Member
edited September 2023 in Magic ETL

Hi, I am trying to figure out how to best solve my issue. I currently have a calculation in my dataflow attached to a certain account code for our organization. That value changes daily but I need to use that value to divide the rest of our account codes by so I can't just add a constant value. Because it is attached to the one account it is showing 0 for all of the others. Is there a way to make it so the value is being used can be used for all of the other account codes since I ultimately need to exclude the account that the original calculation is created with in the card view. Maybe there is a way to do it within the dataflow?

Answers

  • GrantSmith
    GrantSmith Coach

    If you have a constant value from your calculation you can apply that value by doing a Cartesian join. To do this use a formula tile to add a new column called Join and set its value to 1. Feed this into a filter to only get your single value for the specific code. Then duplicate that filter and change it so it’s getting all values except your specific code. This should be coming from your formula tile and not the filter tile. Then take the output from your two filters and join them together based on the Join column your defined in the formula tile earlier. Rename the columns as necessary. You should now have an output with your original data and the calculated value for each row

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

    Hi Grant,

    I am a little confused on what the filters you are talking about. Am I suppose to be filtering on the account code that the calculation is attached to? and then lead that into another filter with everything but that account code? Also should the first formula tile be connected to my input dataset or should it be after the calculation formula?

    Thanks/

Categories