Reporting and Analytics for Service

Get Involved. Join the Conversation.

Topic

    Scott Heidenreich
    distinct group_concatAnswered
    Topic posted January 8, 2019 by Scott HeidenreichSilver Medal: 2,000+ Points 
    62 Views, 30 Comments
    Title:
    distinct group_concat
    Summary:
    How to get a group concat to only show distinct values?
    Content:

    Hello,

    I have a parent table with two children tables (siblings).  I need information from one child table summed and information from another child table group concatenated.  I have the report working as intended, however, the group_concat function is repeating the values for child table 2, once for each member of child table 1 even though child table 1 is only represented in the report using a Sum function.

    How do I get the duplicate group_concat values to be eliminated? In otherwords, I want the group_concat function to only show unique values.  I've tried distinct and first and last without success.

    Thanks,

    Scott

    Version:
    19C

    Best Comment

    Sebastiaan Draaisma

    Done :-)

    The code used is:

    // Assign column id 0 (containg your group_concat) to a variable, this is a string
    $var = $rows[0][0]->val;
     

    // Explode the string into a comma seperated array
    $array1 = explode(',', $var);

    // Create a unique array and implode this back to a comma seperated string and assign this value to column id 0
    $rows[0][0]->val = implode(",",array_unique($array1));

    With custom scripts (essentially just PHP), every column has an id number starting from the left with 0. This id is used within the script $rows[0][0]->val adding a column to the far left will require you to adjust the code. Have a look at my tutorial for beginners in custom script. (easier than you think) smiley

    A 'How to' on using regular expressions in custom scripts for GDPR

    Comment

    • Sebastiaan Draaisma

      I see you used column id 3
      $rows[0][3]->val

      Thanks for spending time on this! I attached the report description. This is the scripting I have:

      // Assign column id (containing your group_concat) to a variable, this is a string
      $var = $rows[0][2]->val;
      $var2 = $rows[0][3]->val;

      I think this may be your error as the picture seems to indicate you would like to use column id 6

    • Scott Heidenreich

      Yeah, sorry about that, I had moved column id 6 to be in place 3 to see if the sum columns were causing trouble - it didn't work their either.  I changed it back to 6 in the scripting and it still doesn't work.

    • Sebastiaan Draaisma

      I will have a look at your report :-)

    • Scott Heidenreich

      Thank you.

    • Scott Heidenreich

      Yes, I did.  Sorry about that.  It is attached now.

    • Sebastiaan Draaisma

      I sucessfully used the following code for accounts:

      // Assign column id (containg your group_concat) to a variable, this is a string
      $var1 = $rows[0][2]->val; // Country
      $var2 = $rows[0][6]->val; // ABC

      // Explode the string into a comma seperated array
      $array1 = explode(',', $var1); // Country
      $array2 = explode(',', $var2); // ABC

      // Sort the arrays
      asort($array1);
      asort($array2);

      // Create a unique array and implode this back to a comma seperated string and assign this value back to your columns
      $rows[0][2]->val = implode(",",array_unique($array1));  // Country
      $rows[0][6]->val = implode(",",array_unique($array2));  // ABC

    • Scott Heidenreich

      I took that script and dropped into my report - no luck unfortunately.  I see below.  The issue is only happening when child 1 (County) has two instances (see highlighted below).

    • Sebastiaan Draaisma

      That should be of no importance.
      How many records does your report show. Maybe f its an exceptional large amount of records (in the tousands) and your Subtype contains a lot of data that it somehow fails to load the PHP (I have seen this before in a report)

      If you have a lot of records, try to use a date inteval to limit your records to 100, just to see if that could be the problem. If not than I'm afraid I'm out of ideas for the moment..

    • Scott Heidenreich

      There are only 264 records in the join total.  Below is a view of the data from the parent (Incidents) and the two children (County and Subtype).  The highlighted records is a single incident showing the records I'm trying to get uniquely group_concat.  The last two columns are the focus.  I filtered down to just one incident - and it is still an issue.

      What it is doing is successfully doing a unique group_concatenate, but then adding another B at the end, or it isn't removing the B during the array_unique.  Below is the data from the join, then the group_concatenated version.  I added "ar2," to the array implode command so I could see what is going on.  As you will see all of the items except B are unique and the second B doesn't have an ar2 after it. 

    • Scott Heidenreich
    • Sebastiaan Draaisma

      I will have to look at it tomorrow with a fresh mind :-)

    • Scott Heidenreich

      Hi Sebastiaan,

      I've done some playing around with this issue and it doesn't appear to be related to the join or the custom script at all.  If I take just a straight join between the parent and the ABC child, do a group concatenate and then have the group concatenate function sort by any of the fields in the ABC lookup table or in the child table, it still doesn't sort correctly, even without any custom script or the second child in the join.  I checked the display order and it should display alphabetically, but to no avail.  I've decided to change the ABC to acronyms for activities that way it won't matter what order they are sorted in...  But it sure would be nice to understand why they aren't sorting properly,  do you think there is anything that can be done with the assort command to fix this?

    • Sebastiaan Draaisma

      Sorry for the late reply :-)

      Glad the custom script works fine. Yes it might indeed be the asort which seems to be used for associative arrays. This would be a 'normal' array (no key + value). Try sort()

      https://www.w3schools.com/PHP/php_arrays_sort.asp

      sort() - sort arrays in ascending order