General ledger transformation

[Jaloy_1973]

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

[gawa]

@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]

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]

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]

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 · Accepted solution]

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

[Jaloy_1973]

Finally it worked.

Thanks a lot Chris. 

Jordi