XPath aggregate functions question

OL Connect Professional DataMapper
1

I have an XML with this structure:

ExportInvoice
Invoice
HeaderData
InvoiceAddress
DeliveryAddress
SalesTable
InvoiceLine
CITLineAmt
SLSItemGroupId
InvoiceLine
CITLineAmt
SLSItemGroupId
InvoiceLine
CITLineAmt
SLSItemGroupId
InvoiceLine
CITLineAmt
SLSItemGroupId

In Settings I have XML elements set to /ExportInvoice/Invoice.

In the extract step I would like to get the sum of CITLineAmt for all the InvoiceLines having the value ‘tr1’ in the SLSItemGroupId node.

I already have created a boolean field that checks if there is any InvoiceLine having the value ‘tr1’ in the SLSItemGroupId node. I used this XPath expression to get that and it seems to work fine:

count(./Invoice[1]/DeliveryAddress[1]/SalesTable[1]/InvoiceLine[SLSItemGroupId=‘tr1’]) > 0

So I thought I could use something similar to get my sum. One complication is that the values in CITLineAmt use period for thousand separator and comma for decimal separator. I’ve included the translate function to solve that.

sum(number(translate(./Invoice[1]/DeliveryAddress[1]/SalesTable[1]/InvoiceLine[SLSItemGroupId=‘tr1’]/CITLineAmt[1], ‘.,’, ‘,.’)))

It doesn’t work however. The translate function doesn’t really seem to be the problem but rather the xpath expression.

How can I do this (preferably without javascript)?

2

You were close… The following XPATH expression should give you the proper result:

sum(./Invoice[1]/DeliveryAddress[1]/SalesTable[1]/InvoiceLine[SLSItemGroupId='tr1']/CITLineAmt/number(translate(translate(.,'.',''),',','.')))
3

It sure is closer to where I want to go, but there is still a problem. I’ve put your expression in the XPath field of the Field Definition of my Extract Step.

When I set Type to String a CITLineAmt of 60,10 returns 60.1 in the datamapper.

However this should be considered a currency, so I’ve changed the Type to Currency but then 601.00 is returned in the datamapper.

Similarly a CITLineAmt of 4.684,50 (four thousand six hundred etc.) returns 4684.5 with Type = String and 46845.00 with Type = Currency.

In case it matters:

I’m very new to Connect, so maybe keeping Type set to String is no problem, but I just want to make sure that in the Designer my “calculated field” will be handled as currency so that I can do something like:

results.html(formatter.currency(record.fields.sumCITLineAmt));

and not as a string.

Thanks for your help. Much appreciated.

4

When you set the field to Currency type, you have to make sure the Decimal and Thousand separators are set properly. Since the XPATH expression returns the decimal separator as a dot (.) and NO thousand separator, that’s what you must specify as the Data Format for that Currency field. Of course, you must also remove any Currency sign, since the XPATH expression does not return any…

5

Oh, I see. Due to the translate steps in the XPath the returned value has other separators than those defined in my default data settings.

Thank you so much for your help!