Kettle: Want to map string value to id from another table

Question

I want to map a string from table1 that has this structure:

                          Table1
                             id
                             value[string] (this can be repeated)

To Table2:

                          Table2
                             id
                             value_id

So there's another table3 i created, where i have all different values from table1.

                          Table3
                             id
                             value(this is unique)

So, the intention of this mapping, is to set a value_id instead of the value, so the question is, how to do this?

Answer 1

Make a transformation as this: 'Table input' -> 'Database lookup' -> 'Table output'

Table input

select person_id, school_name from Table1

Database lookup

• Lookup table: Table2
• The keys to lookup the values: school_name = school_name
• Values to return from the lookup table: school_id

Table output

• Target table: Table3
• Database fields: person_id, school_id