Today, I will discuss about how to create ETL(Pentaho) Equivalent of case statement in Database. In Pentaho, we have component “Number Range”. See the ETL Design for the same.

In this, I have considered csv file input ,Number Range,Write to log components. In CSV file input, FEB-SAL.csv file which we have considered. See the below SS for the same.

See the below SS for the Number Range Component.


Samsung [CPS] IN

Number Range works on x1 less than equal to(<=) x greater than(<) x2 logic.

Here, if you see, 1000 to 5000 , I have assigned value as “Below Poverty Line” and likewise for other Salaries as well. Hence ,its solving the case statement purpose which we do in database.

See the Execution logs of the same.

Write to log.0 – ————> Linenr 1——————————
Write to log.0 – EMP_ID = 2
Write to log.0 – EMP_SAL = 4000
Write to log.0 – SAL_MONTH = 2
Write to log.0 – DOP = 01/02/2018
Write to log.0 – Range_Name = Below Poverty Line
Write to log.0 –
Write to log.0 – ====================
Write to log.0 –
Write to log.0 – ————> Linenr 2——————————
Write to log.0 – EMP_ID = 4
Write to log.0 – EMP_SAL = 30000
Write to log.0 – SAL_MONTH = 2
Write to log.0 – DOP = 01/02/2018
Write to log.0 – Range_Name = Upper Class
Write to log.0 –
Write to log.0 – ====================
Write to log.0 –
Write to log.0 – ————> Linenr 3——————————
Write to log.0 – EMP_ID = 5
Write to log.0 – EMP_SAL = 8000
Write to log.0 – SAL_MONTH = 2
Write to log.0 – DOP = 01/02/2018
Write to log.0 – Range_Name = Lower Class
Write to log.0 –
Write to log.0 – ====================
Write to log.0 –
Write to log.0 – ————> Linenr 4——————————
Write to log.0 – EMP_ID = 6
Write to log.0 – EMP_SAL = 12000
Write to log.0 – SAL_MONTH = 2
Write to log.0 – DOP = 01/02/2018
Write to log.0 – Range_Name = Middle Class