Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Transaction posts over several rows, REGEX

Sailor
7 - Meteor

Hi

 

 

I think some regex expertise will solve this complex data and its relationship. "VER A 101" have some transactions below. All marked with #TRANS, they belong together. There can be any number of #TRANS within a #VER. If transdate is blank in "TRANS, use  VerDate in #VER

 

Original data:

#VER A 101 20161231 "Specification text 2016.S.12" 20161207
{
#TRANS 7511 {} -0,35 "" "" 0
#TRANS 2730 {} 0,35 "20170102" "This is text" 0
#TRANS 1215 {} -100 "" "" 0
#TRANS 2730 {} 100 "" "" 1
}

 

Desired result:

TypeSeriesVerNrVerDateVerTextRegDateSign 
VERA10120161231Intfodran mar 201620161207  
        
TypeSeriesVerNrktoAmtTransDateTransDescQty
TRANSA1017511-0,35  0
TRANSA10127300,35  0
TRANSA1011215-100  0
TRANSA1012730100  1

 

Any idé of how to solve this?

 

Regards Mats

2 REPLIES 2
estherb47
15 - Aurora
15 - Aurora

Hi Mats,

Please see the attached workflow. In order for you to pull in the Version Date into the missing transaction dates, the data all needs to be in the same row. You can then separate the tables out as you presented using reporting tools.

This workflow presents everything in one table.

First step was to remove the curly brackets, replacing with nothing, because they don't add any value. Then filter out the empty rows that result from that replacement. I used a formula tool with Regex replace.

Next, I wanted to assign a key field to each new VER piece of data, in case you have more than one streaming in. Separated the VER rows from the TRANS rows with a filter, used the Record ID tool to assign a number to each of those VER Rows (made up some data so my text input has 2 of these), then joined that information back to the original data. Sorting back to the original sort order, I filled in any missing Group ID (what I called the key field) with a multi-row formula tool.

Finally, the RegEx. I used the parsing method. For the VER rows:

image.png

and for the TRANS rows:

image.png

I hope this makes sense and accomplishes what you needed. Let me know if you have further questions

Sailor
7 - Meteor

Hi EstherB47

 

Thanks a lot, it worked after small adjustments, REGEX_REPLACE had to be changed to REPLACE. I guess it could be that I am running on an older version at work

 

Very nice solution. All data in one table is just fine.

 

Regards Mats

Labels