Challenge #188: Difficult, but not im-parse-able

A solution to last week's challenge can be found here!

This week's Challenge come to us from @T_Willins!

Your tax group has advised you that the information they need is available only in a .TXT report. To properly import the data into a subsequent system, they need the data filtered and in a specified format.

First, match the ID no. from the totals rows to the corresponding detail lines. Then filter to only detail rows with a tax codes of P1 or P9. Format, sort order and field names on the output file must match the given output for the file to load correctly.

challenge_188_start_file.yxmd

challenge_188_solution.yxmd

Parse

Join

Preparation

Advanced

Data Analysis

Difficult

Accepted answers

All comments

OllieClarke

I'm so thankful for RegEx

Spoiler

challenge_188_OC_solution.yxmd

NicoleJohnson

I got 99 problems and at least 98 of them are RegEx. 🙂

Spoiler

Cheers!

challenge_188_NicoleJohnson.yxmd

philipmannering

Copied Ollie,

Spoiler

Workflow

Challenge 186 - Difficult, but not im-parse-able.yxmd

patrick_digan

I'm on a roll over complicating these last few challenges.

Spoiler

challenge_188_start_file.yxmd

justindavis

Start file contains a few rogue tools, at least when I downloaded it... Was that intended?

Spoiler

challenge_188_solution_justindavis.yxmd

Blake

This was a fun one!

Spoiler

challenge_188_BH.yxmd

paulfound

may need to up my Regex game...

Spoiler

Edited: better image of workflow. 🙂

challenge_188_solution_PF.yxmd

hbraunius

My solution attached.

challenge_188_solution_hb.yxmd

LiD

Spoiler

challenge_188_end_file.yxmd

T_Willins

I resolved this from scratch as it was almost a year ago that I first solved this real-life challenge. Much more efficient workflow now. I use this as an example of the power of Alteryx. This used to be a manually copied to Excel process from multiple files that took about 10 hours to complete for over 1500 lines. Now it is seconds.

Spoiler

challenge_188_solution.yxmd

pgrug

Thanks for making the challenge!

challenge_188_solution_Gruggen.yxzp

benakesh

Spoiler

Initial filter : REGEX_Match([Field_1], '^\|\s{1,2}\d{1,10}\s.*')
Order of data in valid rows : Start with | , 1 or 2 spaces , 1 to 10 digits, space and anything

challenge_188_bh.yxmd

TonyA

Talk about a real life example! I never would have figured this out from the instructions without seeing the output. This is why when anyone asks for help on the discussion boards we always ask for sample input and sample output.

challenge_188_TonyA.yxmd

Sebenius

Regex is a blast as normal.

Big tip for geting some help with that regex: https://regex101.com/

Spoiler

Regex Used:
Vat No:
.*\|(\d{3}\-\d{2}\-\d{5})\|.*
Data:
^\|\s+(\d+)\s+(..)\s+(\d{4}\-\d{2}\-\d{2})\s+(\d{4}\-\d{2}\-\d{2})\s+([\d,-]+)\s+(..)\s+([\d,-]+)\s+\|