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 and date column issue

Comments

3 comments

  • Avatar
    Adrian Williams

    Here are some possible options (see attached data flow).

    From the example in the spreadsheet it looks like a simple aggregation is required rather than using the Pivot Table node.

    So, assuming this is the input data

    A legacy Filter(Superseded) node would be configured to build the date string in DD/MM/YYYY format.

    The configuration would be:

    (the fields a, b, c are not required to be output - they are only there for debug purposes). The output of the node is:

    The Pivot Table node would then be configured as follows:

    Which results in the following output:

    Alternatively, an Aggregate node would provide the data you indicated in your spreadsheet:

    You could also use a Modify Fields node to convert the original data to a Date type and change its name to "Date". 

    You can then use the data with a Pivot Table node as before, or use an Aggregate node to sum the data and another Modify Fields node to convert the Date field to a string with a format of dd/MM/yyyy for publishing.

     

    Attached files

     

    Pivoted_Dates - 28 Jan 2021.lna

     

     

     

     

     

    0
    Comment actions Permalink
  • Avatar
    Rubal Grewal

    Thank you so much Adrian for the quick support. Really appreciate it. But can you please let me know if it is possible to append the data in excel and update pivot instead of recreating it. As when same file is read and updated , there are high chances that some details may get missed and overwriting the file may result in "loss of data"

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    The Append Excel node can be used to update an existing workbook. See the node help for details.

    In this example, the name of the input pin of the Append Excel node has been changed from 'in1' to 'RawData' (select the node, switch to the 'Define' tab in the Properties panel, scroll down to the 'Inputs' section and change the 'Input Name' value). The Excel workbook should contain a worksheet with the corresponding name ("RawData" in this case). The Append Excel node is configured to identify the target worksheet by name.

     

    0
    Comment actions Permalink

Please sign in to leave a comment.