Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
The Analytics Swiss Army Knife
A couple of weeks ago, our illustrious Finance Director came to me with a challenge: he wanted to simplify the paying of expenses in our German team by automating the creation of SEPA files. I'd never heard of SEPA files before, but once he said they are written in XML I was happy to take on this parsing challenge. I knew Alteryx Designer would be my tool of choice as it can read and write almost any file type and is also excellent at data manipulation. My plan was to create an analytical application that would take a template Excel file and generate the SEPA file from it.
The final app
So What Is A SEPA File?
"A SEPA file is a type of electronic document used for payments within the Single Euro Payments Area (SEPA). SEPA is a payment-integration initiative of the European Union aimed at simplifying bank transfers denominated in euro."
- Chat GPT
It is an XML file which contains 3 main bits of information. Headers, Payment Information and Transaction Details.
Headers contain general information about the file, such as when it was created, how many transactions are contained in it, the total of all the transactions and the initiating party.
Payment information is information about the payments of the expenses, such as the debtor name, IBAN, BIC and required execution date.
Transaction details are the name and IBAN of the creditor, the amount to be transferred and a reference.
There are other fields within the XML such as nested IDs and some constant values too.
Excel Template
As is made abundantly clear on Alteryx's swag store, Excel is not a database.
Alteryx swag store
However, it is the right starting place for this project. We don't want to fully automate the paying of expenses (there should be some oversight) and we can easily build a template within Excel that can be quickly edited.
In the end, I decided on 3 sheets:
-
Control
-
Transactions
-
IBAN Lookup
The Control sheet contained the file-wide information mostly associated with the payment information section of the SEPA file.
The Transactions sheet contained the individual transactions which would populate the SEPA file. The ID field is a linking field to the final sheet to get IBAN information.
Finally, the IBAN Lookup contains the linking ID, Name and IBAN of all possible payees.
When creating the SEPA file, the user just needs to put the desired transactions into the Transactions sheet, ensure the IDs are correct, and update the relevant control rows (probably just execution date).
So we now had the base data required to make the SEPA file in an easily digestible form for both user and machine.
SEPA Structure
Having got our input data in an easy-to-maintain form, I now needed to understand the output format. As mentioned earlier, this file is made of XML metainfo, and then 3 component sections: Headers, Payment Info and Transaction Details.
The information within this file is either directly sourced from our input template, calculated from those values, or is a constant within the file.
I worked out the source of the values in the file from some documentation I found online along with a previous file which had been validated to work. As I mentioned earlier, this project was about creating a single SEPA file for payment of expenses, rather than a general SEPA document producer. As such where the documentation I found differed from the previous file, I followed the example of the previous file. I've also set certain values as constants which may not be in other scenarios.
These constant values are:
-
Payment method
-
TRF
-
-
Service Level Code
-
SEPA
-
-
Charge bearer
-
SLEV
-
-
Purpose Code
-
EXPE
-
-
Currency
-
EUR
-
I generated a Message ID value which would be unique for every SEPA file created based on the runtime of the app combined with a random 2 digit integer. I used this value for the Payment Info ID too, and suffixed it with a row id of each transaction in order to create the End to End ID.
e.g.
Message ID = 2024070116564521
Payment Info ID = 2024070116564521
End to End ID = 2024070116564521-0000001
Time for Alteryx
So, I now had all my data, and an understanding of the structure I needed; all that was left was to bring it together. My app would input all the data, constants, calculated values and header names. Then do some data checks to ensure no erroneous outputs. It would then construct the XML for each of the 3 sections, taking similar approaches but reflecting the slight differences between them. Finally it would bring everything together and output as an xml file.
Nesting
The building of the XML is fairly straightforward, with one exception. XML values are indented to show the nesting of them. So in the screenshot below, the SEPA value is the Code of the Service Level, within the Payment Type Information, within the Payment Information, within the Customer Credit Transfer Initiation, within the SEPA Document.
I had to build my app such that it would properly nest an unknown number of transactions and ensure that the indentation of the output was all aligned.
In my XML headers lookup file, I included all values within the section separated by a "." (so SEPA above would have the header PmtTpInf.SvcLvl.Cd) I could then break the values out by "." and count the nested level.
Breaking out the nest
To build the XML for a section, I need to open the tags in order, then put the values in, then close the tags (also in order).
Opening the tags can be done by surrounding the header in chevrons (as in <Amt>). Closing the tags can be done by prefixing the header with a forward slash, and then surrounding in chevrons (as in </Amt>). So the final tag, value, closing tag would be <Amt>0.05</Amt>.
In order to sort the values, all opening tags have a base sort of 1, all values have a base sort of 2 and all closing tags have a base sort of 3. I then add on a sub-value derived from the nest level to properly order everything.
//Opening tag sort
1 + ( [nest] / 1000 )
//Value sort
2
//Closing tag sort
3 + (( [Max_nest] - [nest] ) / 1000)
For the indentation, I also made use of the nest level. prefixing 1 tab character per nest level.
//Indenting all xml correctly (ASCII 9 is a tab)
PadLeft([Value]
, LENGTH([Value]) + [Nest]
, charfromint(9)
)
So having built the xml for each of the 3 sections using this approach, I can bring them all together, add the constant XML meta info opening and closing tags and then I've got my final SEPA file data ready.
I create a unique filename by appending the start time of the workflow to "SEPA_" and then I'm ready to output.
//Filename
'SEPA_' + REPLACECHAR( datetimestart(), '- :', '') + '.xml'
XML is not a supported output filetype for Alteryx, but we can work around that by outputting the data as an undelimited csv ("\0" as the delimiter) with the file extension changed to .xml
By using a relative filepath, outputting to the same directory as the workflow will allow users to download the output from the Gallery, and has the added benefit of outputting the xml to a temporary directory, so we don't leave people's IBAN numbers exposed after the app has finished running.
Once published to Gallery, all the end user has to do is upload their Excel expenses template and hit run, and they get a properly formatted SEPA xml file within seconds which they can download and use to expedite payment of expenses.
And That's It
Thanks for reading this blog, I hope you found it useful or interesting. If you do have any questions you can find me on twitter/linkedin/maveryx community.
I've shared the expenses app and Excel template here in case you're interested. I will repeat that this app was an ad hoc solution for a very specific problem, and almost certainly won't serve any other situations in its current form. I've written this blog to share how I approached the creation of an xml document in Alteryx, as there will be commonality with other documents.
If you are looking for aid in creating xml documents using Alteryx, then The Information Lab can absolutely help with that! You can find all our contact information here.
This blog was originally published on theinformationlab.co.uk
