Concatenating Header, Detail and Footer in one File

[brandt3076]

I have currently created a workflow and want to add three different outputs, header, footer and detail into one file.  I am struggling to union due the three workflows due to the different number of columns.  Has anyone run into this issue previously?  I am trying to export to a pipe delimited file and do not want to manually add the header and trailer to detail file.  Any help is appreciated!

H|FILENAME|DATETIMESTAMP|GROUP|GROUPNAME|BATCHNUMBER

D|PERSON|YEARMONTH

D|PERSON|YEARMONTH

D|PERSON|YEARMONTH

T|COUNT

[pcatterson]

You could union them by position, not by name.  That instead will align them, but they will probably be a mix of datatypes, which could be problematic.

[brandt3076]

The issue is when I have to export to a file I will get unwanted delimiters for fields which are supposed to be there... Since Header has 6 fields wouldn't I get six delimiters when exporting where for Detail I would only want two delimiters and Trailer I would only want 1.

[pcatterson]

So you are saying that the file looks like:

H|FILENAME|DATETIMESTAMP|GROUP|GROUPNAME|BATCHNUMBER

D|PERSON|YEARMONTH|||

D|PERSON|YEARMONTH|||

D|PERSON|YEARMONTH|||

T|COUNT||||

Why is that problematic?  Are you looking at it in a text file as the output?

Best

Paul

[brandt3076]

The system that is reading this file is programmed to split the file by each record type, header, trailer and detail.  The program is only expecting three fields and will fail if there are 6 fields within that record type.

[SeanAdams · Accepted solution]

The way I'd do this is:

- Split the data into 3 streams (Header; Detail & Footer)

- For each, concatenate all fields into 1 long-text column (you could call this RowText, and it contains the | delimited data for that row-type).  

- So now you have 3 streams with 1 column each called RowText (which contains the full row)

- Union these 3 streams together in the right order, and you've got the output you need.

Happy to mock this up for you in an alteryx flow if needed, or if this answers the question, would you mind marking this as solved?

Thanks @brandt3076

Sean