Data360 Analyze

I am reading the report for customers where the date and amount transferred is mentioned and create a pivot table. But if I am passing the date attributes then it gets reflected as “28-01-2021” and pivot is able to pick. But the client requires a date in “28/01/2021” and I converted the data into a different format using string but then pivot doesn’t work and Dataverse is not able to parse it as a date. Can someone please help regarding the same. Thank you in advance for your support

Attached files

Pivot_Issue.docx

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

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"

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.