Reporting and Analytics for Service

Get Involved. Join the Conversation.

Topic

    Scott Heidenreich
    distinct group_concatAnswered
    Topic posted January 8, 2019 by Scott HeidenreichGold Medal: 3,500+ Points 
    73 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

      Hi Scott.

      The best way (for me it was) is through a custom script where you create a unique array.
      array_unique() alternatively an associative array

      $var = $rows[0][0]->val;
      $array1 = explode(',', $var);
      $rows[0][0]->val = implode(",",array_unique($array1));

    • Scott Heidenreich

      Hi Sebastian,

      Thank you for your response.  if the field I'm trying to get distinct values for is called "County" how would I use that in the custom script.  I'm not familiar with custom scripts.

      Thanks,

      Scott

    • Sebastiaan Draaisma

      Hi Scott.

      I will try to make an example report for you tonight :-)

    • Scott Heidenreich

      Wow!  Thank you!

    • 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

    • Scott Heidenreich

      Thank you!  I will read through the information and see if I can get this to work.

    • Sebastiaan Draaisma

      You are welcome Scott and if you need any help, just let me know :-)

    • Scott Heidenreich

      Works perfectly, Sebastian.  Thank you so much!  Now I guess I'll need to learn PHP, lol.

    • Sebastiaan Draaisma

      Yeah little PHP will carry you a long way :-)
      Glad I could help Scott. Enjoy your day!

    • Scott Heidenreich

      Hi Sebastiaan,

      I am so close to getting this report to work.  I've attached a screen shot of the report.

      It has a parent (Incidents) and two sibling children (Counties and OPASubcategory)

      I have all the data on the screen, and the counties group_concatenate and are distinct using your solution above.  I even figured out how to sort the array for the counties.

      However, the second child also has a group_concat that needs to have repeating values removed.  I tried adding a second copy of your php script to the Process tab and changed the column ID to the appropriate column, but that didn't work.

      I changed the variable names and array names in the second three lines of the script and that didn't work either.

      How do I get the Subtype column as shown in the screenshot to also show distinct values?

      Thank you for your help.

      Scott

    • Scott Heidenreich

      Just in case it makes a difference, the currency columns are all calculated using the Sum() function on a child 2 column, called "Amount Awarded."

    • Sebastiaan Draaisma

      Ok, let's start :-)

      Make sure you don't have any hidden columns in your report or 'calculated columns' as these can cause trouble with column IDs
      If you have any hidden columns you will have to make them visible and play with the column format (5 pixel width) and borders to make them look invisible.
      (place them all far right)

      When that is done, your ABC should have ID 6
      $rows[0][6]->val

      Since you have country column id 2 I assume your code looks like this at the moment?

      // Assign column id 0 (containg your group_concat) to a variable, this is a string
      $var = $rows[0][2]->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][2]->val = implode(",",array_unique($array1));

      My guess is that you now would have to use:

      // 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

      // 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

      Hi Sebastiaan,

      I actually had that exact scripting done, it doesn't seem to work.  I don't have any hidden columns, and no Computed columns, but the Three columns prior to column ID 6 use the Sum() function to calculate the amounts for the row.

      I noticed single quotes in most of your scripting, but double quotes a few times - does it matter?

       

    • Sebastiaan Draaisma

      Would you be able to export your report for me? I will have to cange the data to something else as I won't have your custom fields but at least I will be able to make it work for you after which you simply change the data back to your custom fields.

      Single or double doesn't realy matter in most cases. Have a look at:

      https://stackoverflow.com/questions/3446216/what-is-the-difference-between-single-quoted-and-double-quoted-strings-in-php

    • Scott Heidenreich

      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;

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

      $array2 = explode(',', $var2);
      asort($array2);

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