Hello,
I want to automate the loading of a statement.
To do this, I have a database that assigns each keyword in a label to a category.
I would like that when I reload my statement, Pigment recognizes the keyword in each label and assigns it to the corresponding category thanks to the created database.
What formula can I use?
Thank you for your help
Best answer by Benoit
View original
+14
Hi Myriam,
Could you describe a bit more the data structure at play here, and the constraints you want to have in here? e.g. is it a transaction with one property per label, or just a text property with labels separated by commas?
A data example / a diagram would be very welcome.
Also, what are the volumes here, e.g. transaction count and number of labels?
This will have a big impact on what I could recommend and the time it will take for Pigment to assign everything.
Hi François,
Thank you for your answer.
I have an Excel document in which I have 400 labels.
I would like each wording to be assigned to the category I want based on a word taken from the wording.
Example:
I have a wording like:
234452_CARD_GRYB_12453TRANSACTION/WTC
I would like my Excel to load into Pigment, it assigns my label to the category "IT" thanks to the word "GRYB" present in my wording.
Here is the transaction List I already created:
Also, in my Excel document the data base I would have for each wording is a date and an amount.
+12
Hi Myriam,
You can use the
described in this exemple I’ve made for you:
I’ve created a dimension called SC libellé where I made my labels with their rubrique de compte.
Then, in my data load transaction list which I called “data”, I created a dimension property called “SC libellé” to return the label found, using the contains() function:
I did the same for Rubrique de compte:
Note that these properties are not linked, you can directly add the Rubrique de compte to your data without creating the label one.
So the formula you’re propably looking for is something like:
if(contains('SC libellé'.Name[by constant:data], data.Name ,1,false),'SC libellé'.'Rubrique de compte') [remove firstnonblank: 'SC libellé']
Please let us know if that helps.
Best,
Benoit
+14
That’s an excellent workaround, thanks
While it does work (and I’ve used a similar trick in the past), I have to warn anyone reading this solution that you should keep it 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 them in your source system which may be much more efficient on that kind of task.
Hope this helps!
Hi everyone,
Thank you very much for your quick and helpful answer !
It worked very well
Best regards,
Myriam
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
