General ledger transformation

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

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

Jaloy_1973

Finally it worked.

Thanks a lot Chris.

Jordi

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335