Removing tuples with the same elements in lists

Follow

Tim

• March 07, 2016 15:15

Hello,

assuming I have the following table with sample data (ID is a GROUPBY and list is a GROUPCONCAT of another sheet):

ID | list

1 | [a,b,c,c,b,a]

2 | [a,a,a,c,d,b,a]

 

What I want to do now is remove all pairs with the same elements so that

[a,b,c,c,b,a] becomes [a,b,c,b,a] and

[a,a,a,c,d,b,a] becomes [a,c,d,b,a]

 

How can I achieve this? GROUPCONCATDISCTINCT is not an option since it would remove every reoccuring points it the list.

 

Thanks in advance and with best regards

Tim

 

0

• Official comment

  

  Joel Stewart

  • March 08, 2016 16:56

  Thanks for the additional details Tim and for confirming that you have already made considerations about the sorting to ensure consistent results.

  In this particular scenario, I'd recommend using the GROUP_PATH_CHANGES function.

  Comment actions Permalink
• 

  Joel Stewart

  • March 07, 2016 17:37

  There appears to be an implied assumption in your proposed use-case. Specifically, using the GROUPBY and GROUPCONCAT functions does not guarantee the order of the results between steps. The results may shift order as they are distributed and may complete at different times. 

  For this reason, the GROUPCONCATDISTINCT function is recommended here because it will search globally for duplicates and only return one record per instance. 

  If a use case requires the order of the rows to be maintained explicitly, be sure to use a function such as GROUP_SORT_ASC to maintain the precise ordering. 

  Does this use case require that order be maintained? 

  0

  Comment actions Permalink
• 

  Tim

  • March 08, 2016 09:30

  Thanks for the answer and for the suggestion Joel!

  In fact I have a sorting variable (date) and applied it now on the GROUPCONCAT function sorting the table beforehand by date.

  The problem remains. For further explanation: Imagine these lists as paths through a network where I want to remove the hops to the same location where the subject already is:

  [a,a,a] -> [a]

  [a,b,b,b,a] -> [a,b,a]

  0

  Comment actions Permalink
• 

  Tim

  • March 10, 2016 11:58
  • Edited

  Thanks a lot Joel, now I have every path for 2 successive elements. Finally I want to merge them by their owner to get a full path per owner:

  owner 1: [a,a,a] -> [a]

  owner 2: [a,b,b,b,a] -> [a,b,a]

   

  How can I achieve that? Is there a merge path function or so?

  Thanks again!

  0

  Comment actions Permalink

Please sign in to leave a comment.