Data Studio v2.0 - collective grouping columns

Hi

Hopefully this is a simple question.

In version 1.6.1 to group columns across all data, you would right click on first column and select group

Then any other columns you would select collective columns

This would group based on all columns selected but leave all the columns as is.

In version 2.0 if l right mouse click, group is not an option

I can select icon

data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAADsAAAA4CAYAAABDsYAdAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAAQKSURBVGhD7ZlpSFRRFMdPkeWYZiVTViOZaVIaWpQtYEiRmkIrVBQVfakIiojyW3sErVAQIURGYCsYQRYU0UK2aKCpFKG24FimaIulbTC9/5l7cxxnplfvOTav94PLffe8+5b/Ofeee+dND4cC/Sf0FPV/gSnWqJhijYop1qjoIra++bM4Usentu9c/I3mTUW1/T2t3neNMiaNoPSU4cLqm/35JRQaEkS5OenC4h80i0WE9uUXU1F5nbCoY+38ZFqQNkq0/IMu20UI3Xq8iGJt/Wlq4lBhdVLz+kMnR4RagujS3nmi5T90TVDpKdG0IiuxQ8lQbP8KZjbWG8zN/O3ZXCIH9hVW/6Or2LfNrbwMuRcAkSjIwt2FrgnKnaS4QVxnKEsSliaAZaq+6XPgJyhXkHEPrUvjIoV2N10a2eWZY8RROwW3q7jujsh2y6YCUV8+KyHwNhVIQEu3F/7RdnGbMgoiI/oG3nYRQHCopTdHTA3OHwLf/L4M6SI2UDB3UEbFFGtUTLFGxRRrVEyxRsUUa1S8/hCoa/xEJy5X0K3SWmEhGjvSSpuXTKRh1lBhCSy8it1w+CZV1DRS2rgoGh8/mJ6+bGLhA/sF06ktWaJXYOFR7J0yO+04cY8mjRlCe9akCivRgdMldPXBC9q4eAJlT43hdkS4hUKCg6j27UfapEQdPK5upLvldmr78oOiBvejRTPi2Q7kNSuzE7ldeO85O3Km+Jh+vfgljY6OoIZ3rdT0oa1DX614FHu0oJQKblXRqjlJHV5UgiGOoTxj/Xmy9OlFbV9/sP3GkYX88sculnEbowB9XZ2Ga2JtAyg3Zya3XR0IDp19xPdEAc0fv/Do2rJyCre14DFBISLA25cH9zmLF5VD++SVSn5RfHKBbX5aHD188obyCiv5vBrgjAu7Z3PBMaYPnKYVVdkY3kdEZEFbMswaxkMaDsDwRyQQSemQualxXFfVvuNaDTYXZ05OGMJ1WVUD11rwKNYS7BxC8g9jzCdED1HyRUvrN3HUjhTdpDjhbxg0IEQcacej2LExVq7vV77mOinWytGLjgzntjeSxT8AiK4EyQrE2fpz/acgeemFR7HTkm08FLH0YAlC0jl34xldL3nF52Xk3UEUcZ2co7ju4BnnkJfZFn3qGlv4fijlyjPcgU2el8sdnK0Vn18Xd+Xd5xeX2RYPhRi5xLhnVoBEslO5rtrunKNIVssyE35ldcxrfFTHPXFOZmzXbAyHYHTIPjlLUzgAmoHY33G7tNZhb2gRLXWgv5JURKszns5dLqpxTF93zrE/v5jbeK6eqMrG8KpMNGpBf8x1b/g6J9Elmi6oEusvwkJ687TArqkr8DlnjcY/FdmuxhRrVEyxRsUUa0yIfgKIGqFzRucjwwAAAABJRU5ErkJggg== There was an error displaying this embed.

but this is doing aggregates, lm struggling to work out how to group just 1 or collective fields, remove the aggregate that is within the options and then save this back to workflow

Look forward to feedback on this.

thanks

Carolyn

Answers

  • Clinton JonesClinton Jones Experian Elite
    edited March 31

    Hi @Carolyn as you have observed, grouping in v2 behaves a little differently to v1 but, ostensibly it achieves the same end results.

    So for example in this short video below I took the US cimres data and wanted to see what the count of crime types was, this is simply done by either going into the grouping step and grouping on the columns you wish to group on, or by doing the same in explorer mode and saving to the workflow.

    The latter is probably the easiest way and in the video you will see why, though ultimately the method for grouping is also effectively the same.

    if you want to just see some first or last value in the range for each column irrespective of the criteria you are grouping against then you would just choose an aggregation for the other columns and show the first or last value. By default all columns are hidden with the grouping step in v2.



    In this grid visual I am using the same data but grouping on description and aggregating on the IUCR ID Case Date and Block and just showing the first value that shows up for each of those

    In the step itself you can see the selections I have made


  • CarolynCarolyn Contributor

    Hi

    thanks,  but l still cant understand how you apply this across all fields

    For example

    Say the source has following columns:

    Id            name    surname    dob                   address                  email           phone


    You want to group by ID first, add name and surname to the full row.

    So at end you see the full row of data but grouped first by Id, then name and then surname but also see the balance of columns

    thanks

    Carolyn

  • Clinton JonesClinton Jones Experian Elite
    edited March 31

    Hi @Carolyn ok, for grouping as you describe, there are three choices

    1. Use the Find Duplicates functionality
    2. Use group and then join
    3. Harmonize

    You'll find a load of content on the Find Duplicates functionality elsewhere on the community site and in the documentation and the same is true for the harmonize step.

    Let's assume you want to use the basic option under item 2 and just select any one of the rows.

    For the purposes of this exercise I will assume that the records under the ID's are ostensibly the same and that you simply want to group by ID, that being the case you would do it this way

    In the grouping step

    selecty group on ID then create an aggregate, but a special "Multiple" , select all the columns bar the ID, add them

    You will need ot also select the aggregation type, First or Last Value - your choice.

    you'll land up with a layout that looks like this

    note that in the preview my list of twelve is now down to 4

    if you wanted to retain the 12 records but just group them then you could create a 'clusterID' out of a hash or a sequence or something, you could even use the ID column.

  • CarolynCarolyn Contributor

    Hi

    The grouping in version 1.6.1 to just put cursor on column and right mouse click and select group

    then you take next fields to collective was pretty straight forward

    I only want to group by a few fields but reflect it across the entire rows - this way we have to do all columns is this correct?

    thanks

  • Clinton JonesClinton Jones Experian Elite

    What you would land up with being shown v1.x was potentially a bit random and would have required you doing a whle lot of sort sequencing potentially unnecessarily

    in v2 you have to have an aggregate for any columns that you want to show up but the multiple aggregation option is pretty quick to effect.

  • CarolynCarolyn Contributor

    Ok thank you

Sign In or Register to comment.