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==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
0
Answers
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
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
Hi @Carolyn ok, for grouping as you describe, there are three choices
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.
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
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.
Ok thank you