Hi Eva,

There are many ways to do this in Pigment, the easiest way is to use an IF condition and a function depending on how matching are your texts. In your formula you just need to call both text properties of your lists to compare them. Most of the time, a modifier would be required to get the correct output.

I’ve made a reproduction and a board for you and I hope one of these formulas would help you achieve your need.

Formulas

• IF text = text : if('list A'.Name = 'list B'.Name, true )rremove lastnonblank: 'list B']
• contains : if(contains('list A'.Name ,'list B'.Name Nby constant: 'list A'] ), true ) uremove lastnonblank: 'list B']
• find : if(find('list A'.Name ,'list B'.Name Nby constant: 'list A'] )>0, true ) uremove lastnonblank: 'list B']
• item : if(isdefined(item('list A'.Name ,'list B'.Name )), true )
• mid : if('list A'.Name = mid('list B'.Name, 3 , 20 ), true ) uremove lastnonblank: 'list B']
• startswith : if(STARTSWITH('list A'.Name ,'list B'.Name Nby constant: 'list A'] ), true ) uremove lastnonblank: 'list B']
• endswith : if(endswith('list A'.Name ,'list B'.Name Nby constant: 'list A'] ), true ) uremove lastnonblank: 'list B']
• left (or right) : if('list A'.Name = left('list B'.Name, 3 ), true ) uremove lastnonblank: 'list B']

Please note that if your lists are big, this kind of operation isn’t really performant.

Let us know.

Best,

Please note that this could be a super heavy operation. If your two transactions are a million items, you’re going to ask Pigment to run the comparison a TRILION times, every time a a new file is loaded.

from experience, this is un unusual need, would you like to explain a bit more ?

Thanks for your answers.

Indeed, I was trying the first formula (text) and I assume that the error was related to the # of items ? It seems to work with the “item” formula, at least for now.
Note that transactions are not million items (270k for now)

To give you some context :

I’m importing accounting data (General ledger) from our Netsuite ERP. When the accounting team is deleting and replacing some accounting entries, it happens that old Line Unique Keys remain in the transaction list (and data is not erased from those rows). 

In order to identify duplicate/wrong values, I want to do a comparison between the transaction list I am using and a second import of Line Unique Keys from Netsuite with the “delete existing Items” option on (so that wrong Line Unique Keys are deleted when the import is run)

I am aware that this is not the best option, but since I did not identify the problem at Netsuite/Accounting team process level for now, I wanted to try this temporary solution.

Hope this is clear,

Thanks, 

 

You’re saying some lines might be deleted from netsuite and thus stay in Pigment ?

We have an option for that: delete the whole period and reload.

Look at this article: Loading data to a Transaction List: Import Configuration option | Community (gopigment.com)

section: using Scoped dimension options

Yes but I do not want to delete and reload the whole period as I’m adding override columns (additional manual inputs for some rows) in my transaction list.

My problem is that when an accounting entry is deleted from Netsuite, data of the concerned row does not turn “blank” in Pigment after running a new import (data remains the same). 

That is why I’m trying to do a link between the unique ID of my transaction list (the one with manual inputs) and the unique ID of another transaction list with the option “deleted ans reloaded items” in order to delete manually duplicate/wrong values

I’m trying to find a solution directly in Netsuite/our accounting entry process.

Thanks,

I see. 

One more idea: could you get from netsuite an export of deleted lines (same Unique ID as when you load ) ?

You could load that in Pigment and “deactivate” the line