Collapse columns based on value

user09188
user09188
in Magic ETL

I have a set of Call data where each row contains similar information in all columns but creates a new row based on the "flow" of the call.

 

For example:

 

 

LinkedId 
Leg LegType MenuName MenuOption Initial Queue Name
bos-z15-call3-1550235827.1062618leg 1Dial  curc-iready-q
bos-z15-call3-1550235827.1062618leg 2Answer  curc-iready-q
bos-z15-call3-1550235827.1062618leg 3EnterIVRcurc-auto-main-update curc-iready-q
bos-z15-call3-1550235827.1062618leg 4EnterIVRcurc-auto-main-update_Special Event curc-iready-q
bos-z15-call3-1550235827.1062618leg 5EnterIVRcurc_659502curc-iready-q
bos-z15-call3-1550235827.1062618leg 6EnterIVRcurc-tech-support-update curc-iready-q
bos-z15-call3-1550235827.1062618leg 7EnterIVRcurc-tech-support-update_Day curc-iready-q
bos-z15-call3-1550235827.1062618leg 8EnterIVRcurc_666742curc-iready-q
bos-z15-call3-1550235827.1062618leg 9EnterIVRcurc_32386 curc-iready-q
bos-z15-call3-1550235827.1062618leg 10EnterQueue  curc-iready-q
bos-z15-call3-1550235827.1062618leg 11Answer  curc-iready-q
bos-z15-call3-1550235827.1062618leg 12Hangup  curc-iready-q

bos-z15-call3-1550235827.1062618

leg 13Hangup  curc-iready-q
      

 

I'm trying to determine which calls are abandond from the information. The flow can change depending on the call but I want to seperate out each call that goes to voicemail (a specific LegType) and which calls Hangup without being answered. 

 

The number of Legs can vary. But if they have a status of EnterQueue, I want to call out the ones that go to Voicemail, and ones that hangup after. Any other call will count as answered. In doing so I want to collapse the data and add an extra column that just says "Disposition" and have a value of answered, Voicemail, Or Abandoned.

 

Any assistance would be appreiated.

Comments

  • Unknown
    Unknown

    You should be able to do a beastmode (calculated field)
    http://knowledge.domo.com?cid=beastmodecalculation

    You would want to do a case statement something like

    CASE

    when `Leg` = 'leg 3' then 'Voicemail'
    When `Leg` = 'leg12' then 'Hung up'
    Else 'Answered'
    End

     

    If you do this it should give you the three groups based on those categories. Then you can do a drill path to a card that has the details of each section.

Categories