Find the "COMPANY" code and append it to records for that company

[Bigmonki]

Hi All,

I am bringing an XML file in to Alteryx and compiling for a comparison. Thing is, there are multiple company records within one file and I need to separate the records by company.

The Filename is giving me the start of the string, but I cannot work out how to add the company! So the file will always have the code for the environment  it belongs to (GB 03), then the company code I need is in the data within the file.

Alteryx is creating the column System, what I need is to take the company VALUE of "JA" in this case and add it to the "GB 03" so it looks like "GB 03 JA". Then at the next change in COMPANY, it needs to add that code to the "GB 03" until the next change and so on.

A point to note, the number of records (a manually added field to count not part of the data) varies depending on the version of software being used. Some companies have 71 records, others have 69!

Could anyone assist me please? I have attached my core .xml file and the .xlsx output I get with the two manually added columns (Record count and To look like)

[markcurry · Accepted solution]

I think the Multi-Row formula will do what you want, use the following formula...

IF [Field] = "COMPANY" THEN
    [System] + " " + [Value]
ELSE
    [Row-1:New Column]
ENDIF

Hope that helps

[Bigmonki]

Thanks Mark, worked a treat