I need a formula that is comparing the column “Assignee” and “Inventor”.
If Assignee equals to Inventor nothing should happen. Row0 and Row1 should not be adjusted.
If Assignee is not equal to Inventor like in Row2 and Row3 the certain string value in the column “Assignee” → AUDI should be deleted and replaced by an empty string " ".
Thanks for your help!
Use Rule Engine node
$Assignee$ = $Inventor$ => $Assignee$
TRUE => “”
Select Replace column : Assignee
Thank you, that helped a lot @andrejz
Now I realized a more difficult problem in my database:
As you can see in the screenshots, there are the Inventor names in the assignee columns.
But I like to delete all Inventor Names + Country Codes within the Assignee Columns + Country Codes (Here PETER,DE and HANS,DE)
In my case I need the formula for this precedure:
- Compare the Assignees with the Inventor columns
- If there are the same Inventor names placed in the assignee columns:
Row0: (Assignee 1 = PETER) equals to (Inventor 1 = PETER)
- Delete the Inventor names within the Assignee Columns + Delete the Assignee Country Codes as well
- Copy the Last Column of Assignees → In this case: Assignee_3 = VOLKSWAGEN and Asignee_3_Country= DE into the Assignee_1 and Assignee1_Country column.
The following table should look like the screenshot below:
The formula should work for more Assignees and Inventors (n) and the related Country codes (n)…
I know it is a very specific request of solution, but I hope you can help me with the above mentioned description.
I do not know if I realy understand what you want but I will use more Rule engine nodes, each for one task and append new columns at the end use only new columns …
- compare Assignee 1 and Inventor 1 and append the result in new column “AssigneeNew”
$Assignee1$ = $Inventor1$ => $Assignee1$
TRUE => “”
and add new column “AssigneeNew”
- compare Assignee 1 and Inventor 1 and append the result in new column “CountryCodeNew”
$Assignee1$ = $Inventor1$ => $Assignee_Country1$
TRUE => “”
and add new column “CountryCodeNew”
- compare Assignee 1 and Inventor 1 and append the result in new column “CarBrand”
$Assignee1$ = $Inventor1$ => $Assignee3$
TRUE => “”
and add new column “CarBrand”
You can also check more conditions like
($Assignee1$ = $Inventor1$) AND (Inventor 1 Country = Inventor 2 Country) => $Assignee1$
You can also first concatenate assigne 1 and assigne 2 and then compare them with the concatenated value of Inventor 1 and inventor 2
Hi @8bastian8 , and should be done for other combinations? What should happen for the following example cases?
- Assignee1 = Inventor1 and Assignee2 = Inventor2
- Assignee1 <> Inventor1 and Assignee2 = Inventor2
- Assignee1 = Inventor1 and Assignee2 <> Inventor2 and Assignee3 = Inventor3
- And so on…
And will it always be limited to 3 Assignee/Inventor?
Indead I only consider the rule for n columns, which means that the rule should be developed for
Assignee 1 = Inventor 1…Asignee(n)=Inventor(n).
That is why the last assignee columns, you can see in the screenshot are mostly companies.
These companies I like to put at the beginning starting from assignee_Harmonized…[n], if inventors are first in place before…
So only the first case you have mentioned should be considered.
Do you know the rule for an unlimited amount of assignees/inventors?
I am thankful for your contribution !
Here is my excel file, than you might imagine it:
Example.xlsx (15.4 KB)
have you had any success? If not maybe consider splitting your data on two parts. One with assignee columns and one with inventor columns. This will allow you to use Table Difference Finder node upon which results you can build logic to get what you need.
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.