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:
Type | Series | VerNr | VerDate | VerText | RegDate | Sign | |
VER | A | 101 | 20161231 | Intfodran mar 2016 | 20161207 | ||
Type | Series | VerNr | kto | Amt | TransDate | TransDesc | Qty |
TRANS | A | 101 | 7511 | -0,35 | 0 | ||
TRANS | A | 101 | 2730 | 0,35 | 0 | ||
TRANS | A | 101 | 1215 | -100 | 0 | ||
TRANS | A | 101 | 2730 | 100 | 1 |
Any idé of how to solve this?
Regards Mats
Solved! Go to Solution.
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:
and for the TRANS rows:
I hope this makes sense and accomplishes what you needed. Let me know if you have further questions
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