You are viewing limited content. For full access, please sign in.

Question

Question

Comparing 2 multi-value tokens and capturing only the unique values

asked on July 15, 2019

Hello Everyone,

 

My issue in short that I am trying to compare two multi-value tokens, get only "the unique ones" left and assign on a field. Here is the story;

I have two CSV files coming into a folder. I use pattern matching (\d{10}) to receive ten-digit numbers from these files. Each file can have a different amount of these 10 digit numbers. For example, the first file might have 30 numbers and the second file might have 50 and sometimes they can come with hundreds. Sometimes they have the same amount of numbers too. Also, sometimes these CSV files have the exact same numbers on different rows, randomly. I am trying to create a workflow that will read and process all these 10 digit numbers from both files, compare all of them, delete any number that repeats itself and leave only the unique ones. You can find my workflow picture in attachment. I was hoping that Remove Duplicates token would work but this token still leaves the first value. I am trying to delete all repetitive values including the first value itself and leave just the unique ones. I am not sure how to configure that part.

Here is an example picture of those 10 digit numbers. Numbers in red are repetitive and numbers in white are unique. All red has to be deleted and only white should left.

Please help me with my issue. Thank you very much!

PO Number Comparison Test.png
0 0

Answer

SELECTED ANSWER
replied on July 16, 2019

This workflow does that for me. It assumes that you read the 2 columns from the documents and then combined them into one multi-value token called "All Values".

The Workflow removes creates a token of unique values in the list ("No Dups") and then iterates through these values and counts how many times they appear in the "All Values" token. If they appear exactly once, it adds them to a multi-value token called "Unique Values".

Let me know if you need more explanation for these activities.

1 0

Replies

replied on July 15, 2019

You can use the "remove duplicates" token function on the token resulting from your Pattern Matching activity.

What are you comparing the 2 columns for? Or is just a matter of getting all numbers together and then removing the duplicates?

0 0
replied on July 15, 2019

I tried to remove duplicates on that function but still got the same result.

It leaves the first value intact and deletes the rest. I am comparing two columns to find out the unique numbers. Those columns are copied from two different csv files to an excel sheet. I ran that as a test with conditional formatting to show which ones are repetitive. Like I mentioned, red ones need to go. As you said, my aim is to get all the numbers together and get rid of all the repetitive ones, just leave the unique ones. 

0 0
replied on July 15, 2019

When you say repetitive, you mean you want to only keep numbers that satisfy all 3 conditions below?

  • in column 1 but not column 2
  • in column 2 but not column 1
  • appear exactly once in either column
1 0
replied on July 16, 2019

That's exactly what I want yes.

0 0
SELECTED ANSWER
replied on July 16, 2019

This workflow does that for me. It assumes that you read the 2 columns from the documents and then combined them into one multi-value token called "All Values".

The Workflow removes creates a token of unique values in the list ("No Dups") and then iterates through these values and counts how many times they appear in the "All Values" token. If they appear exactly once, it adds them to a multi-value token called "Unique Values".

Let me know if you need more explanation for these activities.

1 0
replied on July 16, 2019

Oh my god this is amazing this is exactly what I wanted!! I received the correct data. Thank you so much Miruna!!!

0 0
replied on April 20, 2021

What Regex are you using here?

0 0
replied on April 21, 2021

That's a token function not regular expression.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.