Data Studio v2.0 - collective grouping columns

Answers

• Clinton Jones Experian Elite

  March 2020 edited March 2020

  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.

  
  

  Grouping Rows for US Crimes_-_2001_to_present.mp4

  
  

  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

  

  
  

  0
• Carolyn Contributor

  March 2020

  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

  0
• Clinton Jones Experian Elite

  March 2020 edited March 2020

  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.

  0
• Carolyn Contributor

  March 2020

  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

  0
• Clinton Jones Experian Elite

  March 2020

  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.

  0
• Carolyn Contributor

  March 2020

  Ok thank you

  0