Hi i have following data, this column needs to be matched with negative amount
Logic: in the given example 6800 has negative (6800) in 1:3 ratio then it should be matched
the ratio can be 2:3 or 3:2 or 1:1 etc any combination
Value |
0.00 |
(0.00) |
0.00 |
6800.00 |
(6800.00) |
(6800.00) |
(6800.00) |
928.00 |
(928.00) |
(928.00) |
928.00 |
100.00 |
300.00 |
400.00 |
(400.00) |
Expected Output:
Value | Match |
0.00 | M |
(0.00) | M |
0.00 | M |
6800.00 | M |
(6800.00) | M |
(6800.00) | M |
(6800.00) | M |
928.00 | M |
(928.00) | M |
(928.00) | M |
928.00 | M |
100.00 | |
300.00 | |
400.00 | M |
(400.00) | M |
Solved! Go to Solution.
@BRRLL99
It came out a bit length, but I hope it works.
I use the Append tool and filter the match pair.
Hi @BRRLL99
One way of doing this.
Step 1: Record ID tool
Step 2: Filter tool
Contains(ToString([DATA]),'(')
Step 3: Regex tool
Join the True node from Filter tool to Formula tool
Regular Expression: (\d+.\d+)
Output Method: Parse
Step 4: Join tool
Join the Formula tool output to L node
False node output or filter tool to R node
Join by Specific Fields
RegExOut1 -> Data
Step 5: Select tool
From the J node of Join tool, establish the link to 2 select tool
In select tool 1, select only 1st two columns and deselect the remaining
In select tool 2, select only the last 2 columns and deselect the remaining
Step 6: Formula tool
Create a formula tool from both Select tool.
Output column = Match
"M"
Step 7: Union tool
Link L and R node from join tool to Union tool
Then also both Formula tool
So there will be 4 linkages.
Auto Config by Position
Step 8: Unique tool
Select Record ID
Many thanks
Shanker V
Hi @BRRLL99
I have done minor changes to the previous workflow which is highlighted in Green.
Post the changes, it will fetch the expected output.
Many thanks
Shanker V
@BRRLL99 batch macro version