Question

Not sure how to match the data

  • 5 January 2022
  • 4 replies
  • 1 view

Badge

Hello, here is my question. I have two table. The first table is like a glossary. The second table is the input data.

Table 1 (glossary)

​Number Fruit

1-1 banana;apple <= (meaning can be either banana or apple)

1-2 orange

Table 2 (Input data)

Number Fruit

1-1 banana

1-1 apple

So my goal is to see if table 2 match with table 1.

I have a input data (table 2). Both are number 1-1 and the fruit name also match with table 1. Hence, both the result should be passed.

 

However, I am not sure how to do the matching. As the table 1 fruit name is "banana;apple", separating by ";".

 

I would like to have some guidance.

 

Thank you very much!


4 replies

Userlevel 6
Badge +31

There are probably better ways to do this, but I would make the glossary unique to match the input table (AttributeSplitter to create lists for all types of fruit, then ListExploder to create features for all fruit type values in the lists.) then use a FeatureMerger to test if each record from input exists in the glossary. (Merged is exist in glossary, UnmergedRequestor is does not exist in glossary.)

 

Let me know if you need more help.

Badge +10

I'd probably merge on number and then test to see if the Fruits field contains the fruit providing no partial matches are likely to exist

 

imageIf you need to ensure only complete matches you could use a contains regex test instead

image

Userlevel 4
Badge +25

If you know any SQL then you could try the InlineQuerier transformer like this:

 

image 

...then you just need to write a SQL statement that includes your required where clause (either a left or inner join based on the number and values).

Badge +3

If the attribute is a delimtied string.

Turn it into a list.

Do listmerging.

Reply