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.
SOLVED

General ledger transformation

Jaloy_1973
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.

 

Jordi

 
 

 

6 REPLIES 6
gawa
15 - Aurora

@Jaloy_1973 

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.

Jaloy_1973
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.

ChrisTX
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>"
   ENDIF

 

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.

 

Chris

Jaloy_1973
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?

 

Jordi

ChrisTX
15 - Aurora

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

Jaloy_1973
7 - Meteor

Finally it worked.

Thanks a lot Chris. 

Jordi

Labels