Parsing Financial Information Exchange (FIX) files
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi
I am trying to parse a FIX file, which I understand is a type of XML file in that each field within a record has its own header. So in the example below (which I have put into columns), the numbers in black (34, 52, etc) are field headers and those in blue are the actual data.
My problem is that a simple text to column parse as per below will not work as the nil value fields are not recorded and hence the records are multi length (see field headed '6' below as an example).
To parse this I guess I can spend some time writing lengthy IF-THEN statements, but I am just wondering if there is a quick way to parse this using a standard AlteryX tool(s)? I had a go with the XML parser, but did not get far. There are over 300 or so headers so I am hoping I can create the column names dynamically.
If anyone has an existing workflow or any tips, I would hugely appreciate...Newbie to AlteryX, so apologies if I am missing something obvious...
Many thanks!
34=198 | 52=20181106-08:01:25 | 50=XXX | 57=SEIDNI2 | 128=SEIDNI | 6=37.33 | 11=005820181105-DNI | 14=29 |
34=199 | 52=20181106-08:01:29 | 50=YYY | 57=SEIIWA3A | 128=SEIIWA3 | 1=569_C11_TPAB | 6=40.381 | 11=000420181105-IWA3 |
Solved! Go to Solution.
- Labels:
- Parse
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Could you post an example of the raw data?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi David
Here we go
The raw file is in three columns, with the XML data in column 3, separated with pipes instead of '<' and '>', so I am thinking of a way to replace the pipes to turn this into a more standard XML format?
@DavidP wrote:Could you post an example of the raw data?
2018-11-06 | 08:00:23.289_396 | 8=FIX.4.2|9=332|35=8|49=XXX|56=SYS1|34=190|52=20181106-08:00:23|128=802c88|1=802c88_ISA|6=1.2557|11=7314956|14=12|15=GBP|17=EAVVA18KA1117184|20=0|22=4|30=XLON|31=1.2557|32=12|37=OAVVA18KA8302522|38=12|39=2|40=1|48=JE00B6173J15|54=2|55=GCP|59=1|60=20181106-08:00:21|63=6|64=20181108|76=CSTEGB21|110=0|119=15.0684|120=GBP|150=2|151=0|167=CS|207=XLON|10=105| |
2018-11-06 | 08:00:33.579_084 | 8=FIX.4.2|9=393|35=8|49=XXX|56=SYS1|34=191|52=20181106-08:00:33|128=802c11|1=569_C11_TPAB|6=0.2366|11=16669868|14=6061|15=GBP|17=EBSTI18KA1117185|20=0|21=2|22=4|30=XOFF|31=0.2366|32=6061|37=OBSTI18KA8302657|38=6061|39=2|40=2|44=0.2366|48=GB00B0DG3H29|54=1|55=SXX|59=6|60=20181106-08:00:31|63=3|64=20181108|76=WNTSGB2LBIC|110=0|119=1434.03|120=GBP|126=20181106-23:00:00|150=2|151=0|152=1434.03|167=CS|207=XLON|10=178| |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
hi @Amohazab
Once you get the data into the format that you displayed, the attached workflow
1. Transposes
2. Splits the value into Key and data
3. Crosstabs to align all the data under the correct key. The Crosstab accounts for the missing key/data pairs in the records displaying those as empty.
Dan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
High praise indeed from @DavidP. You have to get up early to beat him. Here's an update to mine that starts with your raw data
Dan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Dani and David
Praise indeed - worked pretty much out of the box, thank you!
