Pivot Table with no column specified
Currently, I am trying to micmic the pivot table generated in Excel.
Example:
Input data:
ID FRUIT NUM PRICE
------------------------------------------------
1 APPLE 1 5
2 ORANGE 2 20
3 PINEAPPLE 10 500
1 APPLE 2 10
------------------------------------------------
In Excel - Insert Pivot Table, specify (via drag and drop field names) Rows = 'ID' , Values = 'Sum of num', 'Sum of price'. We have the following result:
ID Sum of num Sum of price
---------------------------------------------------
1 3 15
2 2 20
3 10 500
Grand Total 15 535
-----------------------------------------------------
In Data3Sixty, how can I approach the same result? Specifying the GroupForColumn parameter is a must.
Also, can pivot table exported from Data3Sixty perform the same function as the one generated in Excel? e.g. When I click the entry sum of num for ID=1, it returns the pivot table for ID=1.
Thank you very much for the assistance.
-
The Pivot Table node in Analyze is typically used to aggregate a measure using two specified dimensions. You can specify the same field for both measures.
This would result in the following output using your test data for the sum of the PRICE field:
As only one measure column can be aggregated within a Pivot Table node, you would need to use a second node to aggregate the other measure and then merge the two sets of results data (using a Lookup or Join node):
Which would result in the following:
Alternatively, you could use an Aggregate node to sum both measure fields with the GroupBy fields set to the ID field:
Which would result in:
A second Aggregate would need to be configured to generate the grand total values. This node would not have the GroupBy fields property set:
Resulting in the following:
As the aggregation did not include a GroupBy term, a Calculate Fields node could be used to create the required value of the 'ID' field ( = "Grand Total")
The two sets of aggregated data can then be combined using a Cat node:
Note, the above requires the ID field to be a string type rather than a numeric type.
You could also consider using the 'FRUIT' field as the GroupBy field rather than the 'ID' field, since this would provide more information on meaning of the sub-totals.
See the attached example data flow.
Attached files
Pivot_Table_Fruit_342 - 14 Jan 2020.lna
-
You cannot export a pivot table as an object that can be dynamically filtered - you can only export the data itself.
If the data to be aggregated has been transformed through a number of steps in Analyze you could consider outputting the unaggregated data to a visualization tool (e.g. Tableau, Qlik,etc) so that you can dynamically explore the data within that tool. Alternatively, you could export the data to an Excel file and use an Excel template file to pre-define the required pivot table. In this case I recommend defining the pivot table logic on one sheet in the workbook and configuring Analyze to outputt the data to a separate sheet in the workbook.
The following article discusses using Excel template files:
https://support.infogix.com/hc/en-us/articles/360018961914-Publishing-to-Excel-with-a-Template
Also, this article discusses using the Lookup node to implement functionality similar to a VLOOKUP within Analyze:
-
As another alternative, you could filter the unaggreagated data using a Filter node configured to obtain the filter criteria data from a Run property and then aggregate the data. This requires the data flow to be re-run each time the criteria is changed. If you are using the Server version of Analyze then the data flow can be published to other users (e.g. users with the Explorer role) who can then consume the published data flow and set the filter criteria value as required.
Please sign in to leave a comment.
Comments
4 comments