Collapse columns based on value


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:



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


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.


  • You should be able to do a beastmode (calculated field)

    You would want to do a case statement something like


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


    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.