Pentaho

 View Only

 Get string from month integer

  • Pentaho
  • Kettle
  • Pentaho
  • Pentaho Data Integration PDI
Per Svensson's profile image
Per Svensson posted 02-17-2019 11:37

Hi, I am in the process of creating a data warehouse using a dimensional model and Kettle Data Integration for the ETL process. I have started to create a date dimension containing different columns such as date, year, month, day of week, etc. However, I am having some difficulties converting from date to string. I want to display the month as a string (january-december).

Right now I have a transformation that generates a start date using Generate Row, from that I use Add Sequence to add dates and a Calculator to calculate a row of dates. I format the dates to Date format and calculate Month of date using the Calculator, which displays the month of the date in an integer. This works fine, but now I want to display the month as a string. I use Select Value to change the format from integer to string and set the format to MMMM, but it doesn't work as I cannot get it to display the string. Does anyone know why this is?

Any help is appreciated.


#PentahoDataIntegrationPDI
#Kettle
#Pentaho
Paulo Pires's profile image
Paulo Pires

You can use the 'Switch / case' step to output the string of the month according to what comes from 'calc month of date' step.

Regards

Andry RAKOTONDRASOA's profile image
Andry RAKOTONDRASOA

Hi,

Your transformation is good but just remplace Meta-data > Format MMMM to 0000 in "month_string" step.

Regards

Sparkles Sparkles's profile image
Sparkles Sparkles

Why not do this using SQL instead?

I use the date as a variable for this SQL, handled on job level:

INSERT INTO

DIM_DATE

(ID, `YEAR`, `QUARTER`, `MONTH`, `WEEK`, `DAY`, `DATE`, YEAR_NAME, QUARTER_NAME, MONTH_NAME, WEEK_NAME, DAY_NAME)

SELECT

     '${DIM_DATE}' AS ID,

     YEAR(from_unixtime('${DIM_DATE}')) AS `YEAR`,

     ((MONTH(from_unixtime('${DIM_DATE}')) + 2) DIV 3) AS `QUARTER`,

     MONTH(from_unixtime('${DIM_DATE}')) AS `MONTH`,

     WEEK(from_unixtime('${DIM_DATE}')) AS `WEEK`,

     DAY(from_unixtime('${DIM_DATE}')) AS `DAY`,

     from_unixtime('${DIM_DATE}') AS `DATE`,

     YEAR(from_unixtime('${DIM_DATE}')) AS YEAR_NAME,

     CONCAT('Q', (MONTH(from_unixtime('${DIM_DATE}')) + 2) DIV 3) AS QUARTER_NAME,

     MONTHNAME(from_unixtime('${DIM_DATE}')) AS MONTH_NAME,

     CONCAT('Week ', WEEK(from_unixtime('${DIM_DATE}'))) AS WEEK_NAME,

     DAYNAME(from_unixtime('${DIM_DATE}')) AS DAY_NAME

ON DUPLICATE KEY UPDATE ID = ID;

Per Svensson's profile image
Per Svensson

Thank you for your replies and help! I decided to go with Sparkles Sparklesstrategy and create the date dimension using SQL on a job level.

Data Conversion's profile image
Data Conversion