Pivot Help

Jbrorby
Jbrorby Member
in Magic ETL

Hi,

I have a situation where a column my have different rows of the same values, but the values are in different orders and therefore they are different buckets in cards, when they're actually the "same" thing.

And example of this is in A1:B3 of my attached image, where both rows under the BH_TAT.Antigens column have the same value. This is how these columns come into DOMO in their raw state, being pulled in from a connector from a 3rd party's reports.

What I want is for all selected antigens in the BH_TAT.Antigens column to always appear in the same order, so that when 2 or more rows in that column have the same value, they fall into the same bucket.

My thought to do this (and maybe this is not the best approach, just what came to mind) was split the columns the comma as the deliminator. This works and is shown in A6:F8.

Then, my plan was to pivot (A11:B15), then from there I would maybe take that output into a view and sort by ascending. (A18:B22)

Then I would unpivot (A25:E27) and then I would concat back into 1 column (A30: B32)

But I am struggling with the initial pivot.

"Select the column that has the column labels you want": I would think this would be "BH_TAT.Order_ID"

"Select the column(s) that identify a row": I would think this would be "Antigen 1","Antigen 2","Antigen 3"....etc. from C6:CF

But then I get confused with "New column name to create" , "which label identigies rows that belongs in a new column"


Thanks

dojo example.PNG


Tagged:

Comments

  • GrantSmith
    GrantSmith Coach
    edited March 2021

    Hi @Jbrorby


    Apologies for any typos and the lack of screenshots as I’m on mobile

    If you have Magic ETL 2.0 beta (talk with your CSM to get it enabled) you could try and use the Dynamic Unpivot tile and then after you split your field (importantly here is a select columns tile to drop your original antigen csv list so it doesn’t get unpivoted or you could just add it to step 1 for the columns not to unpivot) based on the comma use that to easily convert all other columns to rows. You’d set the column to not pivot to be your order id field then name the pivoted columns Antigent Number (you won’t use this as this is the original order) and name the other column holding the values to be Antigen. Then use a tank and window tile to calculate the row number (not used just need a function to sort the values within your partition) to sort baed on the antigen field partitioned by your order field. Then use a group by tile to group your data based on the order ID and the use the aggregation option to combine values by comma. You should then be left with the order ID and your sorted list of antigents for each order ID.


    Alternatively you might not even need to progress past the dynamic unpivot section depending on your final analytical needs.

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

    i wouldn't do this in MySQL anymore, I'd do it in Magic 2.0, but this will get you the results you want.


    Basically your initial dataset A1:b3

    Duplicate each row by the number of commas (antigens) order_id 1 has 3 antigens, therefore duplicate the row 3 times.

    then keep the nth antigen.

    your granularity should be one row per order per antigen

    add a constant of 1 called 'isOn'

    then pivot where the label is the antigen name the row_id is the order_id, and the column you create contains isOn and has the name of your antigen.


    https://www.youtube.com/watch?v=oYcpYE7DiV4

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Jbrorby
    Jbrorby Member

    Sorry for the delay, I usually get email notifications when someone responds to my Dojo question, but I didn't this time, so I assumed no one had responded yet.

    I will work with this info and follow up


    thanks

  • Jbrorby
    Jbrorby Member

    Thanks both for the feedback

    I tried Grant's first just because he responded first and it was more similar to what I had already started.

    Grant, I was able to get it where I needed based on what you explained.

    The only thing I did differently was, after the rank and window, instead of the group by, I pivoted back and made the row numbers the columns, then did a combine columns tile.

    For some reason I couldn't get it quite how I wanted using the group by (I was probably doing something wrong) but the rank and window thought was key in getting me where I wanted. I had never thought to use rank and window simply for sorting purposes before.

Categories