Mismatch on the day of week and week name in Pentaho

Question

I am creating a date dimension table using pentaho and im following the tutorial linked https://www.fabguy.de/howto-create-a-date-dimension-with-pdi-kettle.html

but when I preview my data, I found a mismatch between the column day of week and the column week_name : the week_name showed 'Monday' but the day of week showed '2' when the date is 1900-01-01.

The day of week was generated from the calculation 'Day of year of date A' in the calculator, and the week_name was generated from 'create a copy of field A'.

I noticed that the week_name is the correct one, but I really don't understand why the day of week is wrong.

• the whole process looks like this: enter image description here
• the calculator named Calculate additional Dates looks like this: enter image description here
• the mismatch looks like this: enter image description here

I really want to know how to make the day of week become the correct one, if anyone know how to fix it or the reason of it, plz leave a message here!

Thank you very much!!!

Answer 1

By default, Pentaho starts with Sunday (1) if you want to start with Monday try to use the Formula step with the WEEKDAY function instead.

The Formula step has 3 type

1. Type 1: Sunday is the first day of the week, with value 1; Saturday has value 7
2. Type 2: Monday is the first day of the week, with value 1; Sunday has value 7
3. Type 3: Monday is the first day of the week, with value 0; Sunday has value 6

For example, if today is Thursday I will get the following results