Rule Engine (Dictionary) INSTEAD OF VLOOKUP FUNCTION EXCEL?

[8bastian8]

Hello guys,

I have a table creator like this:

And a Rule Engine (Dictionary) like this:

MY WORKFLOW LOOKS LIKE THIS:

The Logic behind:
I like to lookup the “IPC-Hauptklasse” within my Table Creator and like to append a column with “Technologiefeld” within my FILE from the table reader according to the rule as you can see in the Rule Engine (dictionary) node.

Here is a photo from table reader file:

Now i have a error code like this:

Do you know the problem?

BR
Bastian

[takbb]

Hi @8bastian8 , would it be possible for you to upload that workflow, as it makes it much quicker to assist.
thanks

edit: That said, if you are trying to achieve the equivalent of a vlookup, then I think maybe you are better using Cell Replacer, as Rule Engine (Dictionary) is overkill for what you need, and requires more setup.

The attached might assist…Lookup Values.knwf (21.9 KB)

[8bastian8]

Hello @takbb ;

that fits quite well, but I have a problem with the output column, because there are only exact matches shown in the result column.

My logic is: IF my “IPC-Hauptklasse” has a structure like this:

“F01L 1” → it is allowed to do an approximation: So all values are allowed between “F01L 1/0000 until F01L/9999”

But if there is a lookup value within the table creator given with
“F01L 1/12”-> an exact matching should be considered!

Do you unterstand my logic?
In Excel I would use the VLOOKUP with this consideration →
=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)).

So I have to cases:

1. Approximate match (TRUE)
   “F01L 1” → it is allowed to do a approximation: So all values are allowed between “F01L 1/0000 until F01L/9999”

2. Exact match (FALSE)
   But if there is a lookup value within the table creator given with
   “F01L 1/12”-> an exact matching should be considered!

Here is my Workflow and the used excel file:
PATENTDATENBANK.xlsx (1023.5 KB)

Cell_replacer.knwf (14.2 KB)

Many thanks for your help!

BR Bastian

[takbb]

Hi @8bastian8 , thanks for the additional information and I can better understand why the Cell Replacer doesn’t do exactly what you require.

From what youo have said though, it seems that you are wanting a lookup that is significantly better than the “approximate match” provided by Excel Vlookup.

In Excel, if you set the final value of vlookup to false (inexact match), then that simply makes the vlookup assume that your “lookup list” is in sorted order and returns the “highest” value it finds that does not exceed the value being searched for.

For example, in the spreadsheet you supplied, if I were to implement in Excel, the vlookup then:

Any value that is “earlier” alphabetically than “F01L 1” will not match with anything (even it were just “F01L”)

Any value beginning with “G” though will happily match with “F16F 15/31”

Any value beginning with “I” through “Z” will match with “IPC-Hauptklasse”

So I suspect that this is not actually the functionality you are wanting, and are in fact wanting something a little more “intelligent” than vlookup’s so-called “approximate match”

It might be that using a Cell Replacer to find exact matches could be the “first-pass” and then following that, perhaps the Rule Engine (Dictionary) could be used to find inexact matches where values are still missing? I’ll have a play along those lines, and see where it goes, but others might have some other solutions too.

Edit: I’ve had a play and am thinking along these lines, which brings back the Rule Engine (Dictionary) as a second pass. It dynamically creates the rules using String Manipulation

Cell_replacer2.knwf (1.0 MB)

The excel file has been included in the workflow data folder, so if anybody else wants to have a crack at other/improved suggestions it should be simple to do so.

[8bastian8]

Wow, really nice solution!

I’m grateful for you help and your time!

BR
Bastian