Pentaho Spoon Value Mapping

Question

I am new to the spoon tool and I am extracting one table values to load another table with same attributes. In the first table there is a column holding nchar values like 'y' and 'no' which represents(yes and no).

My problem is the second table has a column that will hold these values as a tinyint like 'y' -> 1 and 'n' -> '0'

table1

• y
• y
• n
• n
• y

table2

• 1
• 1
• 0
• 0
• 1

So is there an easy way for replacing these values and changing their types by mapping them. I can do this job in my sql statement like below

SELECT CASE <variable> 
           WHEN <value>      THEN <returnvalue> 
           WHEN <othervalue> THEN <returnthis> 
           ELSE <returndefaultcase> 
       END 
  FROM <table> 

If there are only 'y' and 'n' values this solution would be okay but since there are lots of values in lots of tables like ('a'->2, 'b'->3, 'c'->4) it is kind of a headache for me now. If someone tells me a practical approach I would appreciate it.

Answer 1

Use the value mapper step for something so simple or just use the case statement like you have. I wouldn't store that mapping in the database since it is so simple.

In the valuemapper add a target field since the data types don't line up. Then if you want to rename or replace the old column use the select values step