How do I merge data into single row?

I'm working on creating a card that feeds from our data set of activities. Each activity has a status (1,2,3,4,5) and is tied to an individual client.

 

The table produces a result similiar to this:

 

 Activity 1Activity 2Activity 3Activity 4Activity 5
Client ACompleted    
Client A Completed   
Client A  Not Completed  
Client A   Not Completed 
Client A    Not Completed
Client BCompleted    
Client B Completed   
Client B  Completed  
Client B   Completed 
Client B    Completed

 

How can I merge the information so all client activity results are populated on one line? Would this require a beastmode or is there another option that I am missing?

 

Thanks,

Comments

  • How is the data organized? What does the same output you show there look like in it's raw form?

     

    Is each activity actually it's own column?

  • each activity is individually linked to the client name. So each activity produces its own row, which is what is causing the issue.

  • I have done this in ETL for you:

    The first step is to Group your data - 1.png

     

     

    This will give you the following table - 2.png

     

     

    Then you will need to only select the "complete / not complete" part.  As a side note, I am not very good at regex but I have written a regex that got the job done: ([^Not Completed|Completed]*)([Not Completed|Completed])([^Not Completed|Completed]*)3.pngNow you have the following table:4.png