We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

General ledger transformation

7 - Meteor

Dear Community,


I'm newbie in this amazing world of Alteryx, and I'm struggling with a data set (General Ledger) transformation.

I received from the client a general ledger, where the differents lines of a entry have a header with additional information of these.

I wanted to merger all the data included in the header with the corresponding differents line of the entry.

Please see attached example.


Any suggestion? Maybe a multirow formula? 

Really appreciated for your help.





15 - Aurora


Please be sure of your provided input data is a dummy data. If you uploaded your "real" data, I recommend to replace it, otherwise you might be  complained by your client.

7 - Meteor

Thank you for the suggestion. It was real data, but with some modifications.  I have replaced the example with a random selection of entries of the same dataset.

15 - Aurora

I can't post a sample workflow because my company blocks uploads, for now.  A few suggestions:


In the Import tool:

Start Data Import on line 4 (assuming you don't need any information from the "report header" rows before the data on line 4

Check the box for First Row Contains Data.   This will create each column with a name like F1, F2, F3.  This may help because your true field names are spread across 2 rows.


Use a Filter tool or Data Cleansing to get rid of rows that are completely blank


Combine the first and second rows to get one row for all field names


Use a Multi-Row tool to create a new field (flag) to identify Header rows versus Detail rows.  Use code like

   IF IsInteger([F1]) THEN "Header"
   ELSEIF IsInteger([F4]) THEN "Detail"
   ELSE "<unknown>"


Become familiar with the available functions.  Here's a list: Functions (alteryx.com)


Filter out rows where the new Flag is "<unknown>", or improve the code to correctly classify a row.


Use another Multi-Row tool to assign a RecordID to each group of Headers and Details


Use a Dynamic Rename tool to re-name the fields from F1, F2:  use Rename mode: Take field names from first row of data


Use a Filter tool to separate Headers from Detail rows, based on the new Flag created above


Use a Join tool to join Headers with Details based on the RecordId you created above.



7 - Meteor

Many thanks Chris for your guidance,

I almost have it. 


When you say "use another Multi-Row" tool to assign a Record ID to each group of Headers and Details".


I'm trying with an expression like this, but I'm not sure how to add the incremental record id:


IF [Tag]="HEADER" AND [Row+1:Tag]="DETAIL" THEN RecordID ELSE RecordID+1 ENDIF


Could you give me any additional tip?



15 - Aurora

IF [Tag]="HEADER" THEN [Row-1:Tag] + 1 ELSE [Row-1:Tag] ENDIF

7 - Meteor

Finally it worked.

Thanks a lot Chris.