Sorting by more than one column using GROUPCONCAT

Follow
Daniel Mendez Estrada

I'm trying to find a way to group by one column, and concatenating all the values in another column, all while trying to sort by two other columns. Something like this:

GroupByColumn | SortingColumn1 | SortingColumn2 | GroupConcatValuesColumn

When I create the new sheet, I want to have only the GroupByColumn and GroupConcatValuesColumn, but sort the latter by both SortingColumns1 and 2. I tried doing this:

GROUPCONCAT(#GroupConcatValuesColumn; [#SortingColumn1, #SortingColumn2]), because I know you can provide something to sort by as the second parameter in GROUPCONCAT. This returns this error:

failed with IllegalStateException: datameer.com.google.common.collect.Iterables$8 cannot be cast to java.lang.Comparable

Is there another way that I can accomplish this? Do I need to specify something else?

0

Comments

4 comments

Date Votes
  • Konsta Danyliuk
    • Edited

    Hello Daniel.


    May I ask you to provide an example of the initial data you are working with and the desire result, please? It would be easier to double check the logic, if I would know what is given and what is the expected result.

    Thank you in advance.

    0
    Comment actions Permalink
  • Daniel Mendez Estrada
    • Edited

    Hi Konsta,

    Here is some sample data and the desired result. If you see, I have some two of most offers, so then the next way to sort would be by the date column. I'm trying to find a way to use the GROUPCONCAT function to concat all the statuses for the account, but I want it to keep the order of them in the following sheet. End result of the statuses concatenated is listed at the end.

    acct offer date status
    1761 G 2007-12-30 F
    1761 D 2007-02-30 C
    1761 A 2007-12-30 B
    1761 D 2007-09-30 D
    1761 A 2007-06-30 A
    1761 G 2007-01-30 E

    acct offer date status
    1761 A 2007-06-30 A
    1761 A 2007-12-30 B
    1761 D 2007-02-30 C
    1761 D 2007-09-30 D
    1761 G 2007-01-30 E
    1761 G 2007-12-30 F

    GroupBy  GroupConcatStatuses
    1761        [A, B, C, D, E, F]

    0
    Comment actions Permalink
  • Daniel Mendez Estrada

    I found a solution. Instead of trying to sort by the columns individually, I concatenated both 'offer' and 'date', and sorted by that new column.

    acct offer date status     newColumn
    1761 G 2007-12-30 F    G2007-12-30
    1761 D 2007-02-30 C    D2007-02-30
    1761 A 2007-12-30 B    A2007-12-30
    1761 D 2007-09-30 D    D2007-09-30
    1761 A 2007-06-30 A    A2007-06-30
    1761 G 2007-01-30 E    G2007-01-30

    0
    Comment actions Permalink
  • Konsta Danyliuk
    • Edited

    Hello Daniel.
    If you have below dataset at SheetA:

    acct offer date status
    1761 G 2007-12-30 W
    1761 D 2007-02-30 S
    1761 A 2007-12-30 B
    1761 B 2007-07-30 D
    1761 D 2007-09-30 G
    1761 A 2007-06-30 B
    1761 G 2007-01-30 W

    You could introduce Sort operation by offer type at SheetA to get:

    acct offer date status
    1761 A 2007-06-30 B
    1761 A 2007-12-30 B
    1761 B 2007-07-30 D
    1761 D 2007-02-30 S
    1761 D 2007-09-30 G
    1761 G 2007-01-30 W
    1761 G 2007-12-30 W

    Then create SheetB and at this new Sheet - GROUPBY(#SheetA!acct) and GROUPCONCAT(#SheetA!status). This will give you list of statuses with in order they appear at Sheet1 after sorting.

    GROUPBY(#SheetA!acct)  GroupConcatStatuses
    1761                   [B, B, D, S, G, W, W]

    Is this something you are looking for?

    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post