Data360 Analyze

Hi. I'm new to Dataverse I can see that you can create a simple pivot with one column type and one row type, but is there a way to have multi level groupings? For example using the combination of two different fields for rows. Hope that makes sense!

Regards,

Leigh

Hi Leigh,

can you clarify what you are trying to achieve, perhaps with a short example of the before and after data?

In the interim, here is an example of using the Aggregate node to total some data using multiple grouping fields ...

The following example data can be copied into a Create Data node (use Ctrl-A to select the default data and then Ctrl-V to paste it in):

Name,Gender,Retired,Fruit:int
Ann,F,N,2
Bob,M,Y,4
Chuck,M,N,1
Dave,M,Y,2
Emily,F,Y,1
Freda,F,N,3
Gerry,M,N,0
Helen,F,Y,2
Irene,F,N,1
Jack,M,Y,3

The node's output data should look like the following:

An Aggregate node can then be added to the canvas and connected to the output of the Create Data node.

The Aggregate node can be configured to group the data by the 'Gender' and 'Retired' fields and calculate the sum of the 'Fruit' field for each sub-group:

Hi. Thanks for that, the aggregate function gets me part of the way there, but what if I want to move fields from rows to columns and then apply and aggregate function.

For example say I had the following dataset -

Now I want to take the 'Year Picked' column and split it out into a column for each year. So it would look like this -

So when I say multi level, I mean (as marked in red) having more than one column in the rows grouping when creating a pivot table.

Thanks again,

Leigh

Hi Leigh,

Thanks for the clarification. So in your case the data is only being reshaped in the pivot operation without applying an aggregation operation at that point. Yes, you can do this in Dataverse using the 'Pivot Data to Names' node.

The attached data flow shows an example of how this can be done.

One other point - the values in the NamesField and DataField properties in the Pivot Data to Names node should not be enclosed in apostrophes.

Thanks Adrian for the response.

So in theory that all makes sense!

Unfortunately in practice I'm having trouble with a few errors. Maybe you might be able to shed some light on what I doing wrong!

(Noting that now I am using a real dataset)

 
Kind regards,

Leigh

Hi Leigh,

Can you remove the apostrophes from around the field names in the NamesField property and DataField property (the node is a little picky about the format in those properties but apostrophes are ok in the GroupBy property).  I'm not sure this is the root cause of your problem but its best to eliminate it.

However, its difficult to pinpoint the error without access to the data. Are there any special characters in the values in the NamesField i.e. OFFENCE_SUBGROUP field such as punctuation characters (comma, colon, semi-colon, apostrophes or symbols such as @, etc?). Similarly, are there any special characters in the GroupBy field names? Can you also check whether there are any missing values in the GroupBy fields.

Would you be able to provide a sanitized subset of your data (specifically the fields identified in the node config and at least one other 'payload' field (with some dummy text in it)? I have opened a support ticket for this post so you should receive an email and be able to reply to it with an attachment.