CODEWITHSUNDEEP
sqlpentahoetlpentaho-spoon
mrgenco
mrgenco

Reputation: 348

Pentaho Spoon Value Mapping

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

table2

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.

Upvotes: 1

Views: 2411

Answers (1)

Abercrombieande
Abercrombieande

Reputation: 699

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

enter image description here

Upvotes: 2

Related Questions