We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.

Pivot Table with no column specified

Comments

4 comments

  • Avatar
    Adrian Williams

    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

     

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    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:

    https://support.infogix.com/hc/en-us/articles/360018962094-How-do-I-perform-an-Excel-VLOOKUP-in-Data360-Analyze-

     

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    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. 

    0
    Comment actions Permalink
  • Avatar
    Ron Li

    Thank Adrian for your prompt and detailed solutions. Ya, the Output Excel using template files work perfectly in most scenarios regarding formatting.

    0
    Comment actions Permalink

Please sign in to leave a comment.