Datatable - IF dt contains permutation of datarow


What I have:
I have a csv of 4 values containing all possible permutations of x items (for this example say 4 different strings) with repetition (dt_1). I have a different csv containing some permutations (dt_2).

What I want to do:
I want to check if a row from dt_1 is present in dt_2, irregardless of the order of items… but can’t figure out a way to do this, so far my searches in the forum has left me none the wiser.

Example, row from dt_1 that contains the 257 possible permutations of apples, oranges, banans and grapes:
apple, apple, banana, orange

In dt_2 there is no row with this exact order, but there is the following row:
apple, banana, apple, orange.

I want to say that these two rows match, and copy the rows from dt_1 not present in any permutation in dt_2 to a new datatable.

Help? :blush:

(my first post here after a long time lurking and learning, allow me to use this occasion to thank all the great contributors in here for amazing work - I have learned a lot searching around in here!)

Hi @lindanger

Welcome to the community.

Try this solution. This is just a pseudo code. You need to use proper syntax of either or uipath activities.

MatchesVariable = “False”
For each row1 in dt1
save row1.itemArray in dt1Array
sort dt1Array
For each row2 in dt2
save row2.itemArray in dt2Array
sort dt2Array
if(dt1Array Equals dt2Array)
MatchesVariable = “True”
if(MatchesVariable = “False”)
append dt1Array as new row in new data table

there a at least two approaches:

  • the sort approach - (on ArrayItem Base, On Value Split base)
  • the data Set operations aproach (Intersect, Except)

another helpfully Building block is the sequenceEqual method

with some test data from your side we can help you on some more detailed solution implementation

Thank you both for your quick responses! I think I might need a bit more help to reach my goal. Which, I’m afraid, might seem a bit frivolous… All the same, a great learning experience for me!

@ppr attached you will find the two csv’s. As you can see the permutations are of four elements, while the “tried” permutations contain a far higher number of elements. The plan is to see if there are any untried permutations while adding one element after another to the “all possible permutations” cvs (permutations.csv). The first attempts will likely provide no untried combinations.

@Surya_Narayana_Korivipadu, I follow your logic - I think. But it might proove rather tasking considering the size of the possible permutations, or…?

(note: c̵s̵v̵ ̵f̵o̵r̵m̵a̵t̵ ̵w̵a̵s̵n̵’̵t̵ ̵a̵l̵l̵o̵w̵e̵d̵ ̵f̵o̵r̵ ̵u̵p̵l̵o̵a̵d̵,̵ ̵I̵ ̵h̵a̵v̵e̵ ̵u̵p̵l̵o̵a̵d̵e̵d̵ ̵x̵l̵s̵x̵ ̵v̵e̵r̵s̵i̵o̵n̵ ̵i̵n̵s̵t̵e̵a̵d̵. I’m a too new user to be able to upload files it seems. Can I PM them or something?)

give a try on following:

  • rename csv file to txt on the extension part
    use just a little smple set but complete within the datastructure and create the markdown code, usable here within the post with the help of online tool link below:
    CSV To Markdown Converter

Still cant upload, neither as xlsx nor txt.

I used the converter you provided. Did I do it right? It looks a bit messy.

Here is a snippet of AllPossible4Basic:

And a snippet from AllTried
|476968|2021-Apr-12 10:45:26|cmlr.wam|ENERGY|FLOOD |RAIN |WATER |- |1 |250000 |
|476897|2021-Apr-12 10:42:28|cmlr.wam|ENERGY|RAIN |SEA |WATER |- |1 |130000 |
|476712|2021-Apr-12 10:33:42|cmlr.wam|ENERGY|MNTAIN|RAIN |SEA |- |1 |760000 |
|476703|2021-Apr-12 10:31:00|cmlr.wam|AIR |MNTAIN|RAIN |WATER |- |1 |700000 |
|476624|2021-Apr-12 10:06:24|4rfqu.wam|PLANT |PLANT |POT |POT |- |1 |3640000 |
|476416|2021-Apr-12 09:10:09|c1iba.wam|FLOOD |MNTAIN|MOSS |RAIN |- |1 |1120000 |
|476329|2021-Apr-12 09:00:54|c1iba.wam|EARTH |FLOOD |MOSS |RAIN |- |1 |490000 |
|476305|2021-Apr-12 08:55:26|c1iba.wam|EARTH |MOSS |RAIN |WATER |- |1 |340000 |
|476281|2021-Apr-12 08:49:51|c1iba.wam|MNTAIN|MOSS |RAIN |WATER |- |1 |970000 |
|476148|2021-Apr-12 08:33:37|xqjam.wam|EARTH |FLOOD |MNTAIN|WIND |- |1 |940000 |
|475790|2021-Apr-12 08:02:07|c1iba.wam|MNTAIN|RAIN |SKY |WATER |- |1 |1270000 |
|475578|2021-Apr-12 07:31:39|.ebqw.wam|EARTHQ|ENERGY|MNTAIN|RAIN |- |1 |820000 |
|475235|2021-Apr-12 07:14:23|.ebqw.wam|EARTHQ|ENERGY|MNTAIN|WATER |- |1 |790000 |
|474429|2021-Apr-12 06:38:50|qqlaw.wam|EARTH |FLOOD |PLANT |WATER |- |1 |340000 |

working with following sample data we get the result:

dt1,dt2 is simulating in general your case
ABCD is matching in dt2 DCBA
ABAB is matching in dt2 BBAA
AXYZ doesn’t have a match in dt2 - so we get it within the result
dt2 cols C1, C6 are simulating other columns which we do not need for the check


Assumption data from csv is read into a datatable with read csv activity

Line2 - create an ordered array from the looped dt1 row
Line3 - search for any row in dt2 which ordered 4 cols are equal to arrCheck from line 2
Line3 where - let pass dt1 the filter if there was no match in dt2
Line4 - add all passed dt1 rows to a List

Check defensive if there is an empty result and create or clone the dtResult

Find starter help here:
SeqEquals_CheckPermutation.xaml (12.7 KB)

1 Like

Do you mean that my logic takes too much time to execute. Yes, I know. But I’m able to think of only this solution at the moment.

Awesome. That worked, also tested with additional elements for permutation.

Thank you for clear and concise explanations as well!

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.