Solved

Vendor Mapping

  • 23 May 2023
  • 4 replies
  • 281 views
K
Rank
Badge +1

Hi!
Can someone please help me to find the vendor names from the description of a transaction from a list of vendor list that I already have?

Right now, with the help of ITEM function its able to only recognize if the description has only the full vendor name from the list. But I want to populate vendor names by finding the vendor names in the description among other extra texts as well.

For example,

  • if my Description is “Amazon.com” ITEM function is able to recognize the vendor name and populate Amazon.com from the list that I have as a dimension.

 

  • if the description is “Orders from Amazon.com”, its not able to find “Amazon.com” among the other texts in the description.


So I’m looking for a way to find these vendor names from the description which has other text apart from the vendor name and populate it.

Thank you!

icon

Best answer by Benoit 30 May 2023, 16:53

View original

4 replies

Nathan
Rank
Userlevel 6
Badge +11

Hi,

 

I wrote this article Manage messy & multiple sources for master data: how to manage vendors on Pigment | Community (gopigment.com) about that subject.

You’ll see how to do mapping and avoid this issue you are mentioning, using FIND instead  of ITEM which can only manage exact matching.

K
Rank
Badge +1

Thank you for the suggestion.
Here is more context of the Memo/description and the vendor stage that I currently have.

The vendor name “Endicia” is found in the memo with a bunch of other texts. And ITEM function is not able to populate “Endicia” as the vendor name with this kind of description.

Can you please suggest ways for vendor mapping from these kind of situations?

 

 

Benoit
Rank
Userlevel 6
Badge +12

Hi,

 

Assuming your Vendor dimension is named “Vendor”, you can try or adapt this formula:

IF(FIND(Vendor.Name,'PL GL Data'.'Memo/Description'[add:Vendor])>0,Vendor)[remove firstnonblank:Vendor]

 

We’re saying to Pigment to find any Vendor name within your Memo/Descriptions, and to return the first non blank Vendor.

In the case your description contains 2 Vendor names, the first result within your Vendor List will be displayed according to your Vendor list’s items order.

 

Note:

This solution should be used on small datasets, e.g. small transactions and small dictionaries.

With these formulas, Pigment will make a text calculation of all items of your transactions against all of your dictionary items. While text calculations are always costly (e.g. long to run), they are even more costly if you make tons of them.

The Engineering Team is working on a function that would solve this issue with much better performance (based off the ITEM function, that only works for exact matches currently) but it is not out yet.

 

If you have a large dataset or a large number of labels, I would try to find/map them in your source system which may be much more efficient on that kind of task.

 

 

Let us know if that helps.

Thanks.

 

Benoit
Benji
Rank
Userlevel 5
Badge +8

Hey Keerthana, did you manage to get forward with this? :)

Benji

Reply


Ask your question here!

Terms & ConditionsCookie settings