Solved

Equivalent of vlookup between text format

  • 12 September 2023
  • 6 replies
  • 108 views
E
Rank

Hello, can you please help me with this formula ?

I would like to do a formula similar to vlookup between Unique item values (text format) of two transaction lists (check if a transaction list contains (or not) the Unique item values of the other transaction list → if Unique ID of TL1 is in TL2 then yes, otherwise no) 

Is it possible to use vlookup equivalent between text format values, or do I have to create a dimension ? 

Thanks in advance!

icon

Best answer by Benoit 13 September 2023, 23:12

View original

6 replies

Benoit
Rank
Userlevel 5
Badge +7

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 )[remove lastnonblank: 'list B']
  • contains : if(contains('list A'.Name ,'list B'.Name [by constant: 'list A'] ), true ) [remove lastnonblank: 'list B']
  • find : if(find('list A'.Name ,'list B'.Name [by constant: 'list A'] )>0, true ) [remove 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 ) [remove lastnonblank: 'list B']
  • startswith : if(STARTSWITH('list A'.Name ,'list B'.Name [by constant: 'list A'] ), true ) [remove lastnonblank: 'list B']
  • endswith : if(endswith('list A'.Name ,'list B'.Name [by constant: 'list A'] ), true ) [remove lastnonblank: 'list B']
  • left (or right) : if('list A'.Name = left('list B'.Name, 3 ), true ) [remove lastnonblank: 'list B']

 

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

Let us know.

Best,

1 Attachment
Benoit
Nathan
Rank
Userlevel 6
Badge +6

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 ?

E
Rank

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, 

 

Nathan
Rank
Userlevel 6
Badge +6

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

E
Rank

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,

Nathan
Rank
Userlevel 6
Badge +6

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

Reply


Ask your question here!

Terms & ConditionsCookie settings