Look for value within Column

[BRRLL99]

Hi I have following data-set with 2k Records
same amount needs to be matched with same negative amount

DATA
100
-100
300.01
400.01
-300.01
500
600
6.6
-600
-6.6

Expected Output: When I total "M" values it should be zero

DATA	Match
100	M
-100	M
300.01	M
400.01
-300.01	M
500
600	M
6.6	M
-600	M
-6.6	M

[ShankerV]

Hi @BRRLL99 

One way of doing this.

Step 1: Record ID tool

Step 2: Filter tool

Contains(ToString([DATA]),'-')

Step 3: Formula tool

Join the True node from Filter tool to Formula tool

Output Column: Temp

ToNumber(Trim(ToString([DATA]),'-'))

Data Type: Double

[ShankerV · Accepted solution]

Hi @BRRLL99 

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

Temp -> 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

Many thanks

Shanker V

[BRRLL99]

For Step: 3 
What should be the Formula?

[ShankerV]

Hi @BRRLL99 

This is the formula for Step 3:

ToNumber(Trim(ToString([DATA]),'-'))

[Qiu · Accepted solution]

@BRRLL99 
Do you have any duplicated value in the Data Column?

If No, then we can do it with an Appending Tool?

If Yes, then can you give a sample with expected output?

[BRRLL99]

i didn't check my Values have duplicates

Let me give you realistic data

[flying008]

Hi, @BRRLL99 

FYI.