Can we do unpivot functionality in ETL, in the beast mode?


I wanted to unpivot two value column, let's say, A & B, in which the first column gives A,B (row wise) and the second column gives its corresponding value. Thus, I can use the first column as filter and value column in the card. When we select either A or B, then the values in the card will change according to that. This is achievable through ETL, but Is there any way to make it through beast mode calculations.



Best Answer

  • GrantSmith
    GrantSmith Indiana 🔴
    Accepted Answer

    Hi @Salmas,

    This isn't possible with a beast mode as it's performed on a row by row basis and doesn't have the ability to add rows to your dataset. You'd need to do it with an ETL.


  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    @Salmas what's your use case? what does your data contain and what are you trying to accomplish? it sounds like you have a multi valued column and you want to be able to filter on one value in the multi-valued column.

    you can't do that in analyzer gracefully. what you could do is add binary columns for the major values. isOptionA, isOptionB, isOptionC. This will allow you to filter for all rows that contain OptionA or B or C. but that kinda sucks b/c you could end up adding many columns.

    the alternative is to duplicate your data such that you have a column Report_Option and then unpivot your multivalue column such that the Option is contained in the new column. This way you could select OptionA and see all the rows that contain OptionA. Of course, if you're looking for the intersection of OptionA and OptionB now your data is double counted (OptionA OR OptionB), whereas the initial proposed solution would show you just OptionA AND OptionB

  • Hi @GrantSmith , Thank You