GROUPCONCAT Issue

Follow
Boris D'Souza

Hi,

I have a data set with Event IDs and the activities associated with the event, sorted by Event ID + timestamp in ascending order. I am trying to do a groupconcat to get all the activities within that Event ID in a list which is sequential and aligns with the order of activities which took place. However despite ordering the source sheet, the groupconcat sheet does not concatenate the results in sequential order. Does anyone know a workaround for this?

Thanks.

0

Comments

6 comments

Date Votes
  • Konsta Danyliuk

    Hello Boris.

    Do you think you could provide a sample of your dataset and the desired result, as well as the GROUPCONCAT formula you are using?

    0
    Comment actions Permalink
  • Boris D'Souza

    Attached a sample file here. On the first image, imagine I have sorted the items by EventID, CreateTime in ascending order. Therefore when I do a groupconcat I should be getting the activities in the order of where they appeared in the source sheet. This is indicated in the second image below. However I am instead getting something like this

    Event ID                              ActivityConcatenated

    123456789                         [Activity D, Activity E, Activity A, Activity C, Activity B]

     

    0
    Comment actions Permalink
  • Konsta Danyliuk

    Hello Boris.
    Let's imagine that you have the following dataset with a random order of the column Activity.

    Simple GROUPBY(#Source!EventID) and GROUPCONCAT(#Source!Activity) won't sort the list.

    But you could add the SORT function that does the trick - SORT(GROUPCONCAT(#Source!Activity))

    Alternatively, you could sort the Activity column at the source Sheet fist.

    I hope this helps.

    0
    Comment actions Permalink
  • Boris D'Souza

    Hi Konsta,

    Thanks for your reply. The problem is I want the activities sorted/concatenated based on the order in which they took place (created time). Therefore based on your example, I would want it concatenated as

    [Activity A, Activity E, Activity C, Activity D, Activity B] as this is the order in which they occured. However Datameer is not sorting it for me based on the time it was created.

    0
    Comment actions Permalink
  • Nikhil Kumar

    Hi Brian - GROUPCONCAT takes an additional argument which is the column you want to use to order the concatenation. The following help page actually uses a timestamp to order the concat using the second argument to the GROUP CONCAT function. This should achieve what you are looking to do - https://documentation.datameer.com/documentation/display/DAS70/GROUPCONCAT

    1
    Comment actions Permalink
  • Boris D'Souza

    Thanks a lot Nikhil, didn't even realize you could do that. Really appreciate it. Thank you to Konsta as well.

    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post