tag:blogger.com,1999:blog-15319370.post1966391023234360965..comments2024-03-05T11:16:00.846+01:00Comments on Roland Bouman's blog: Kettle Tip: Using java locales for a Date Dimensionrpboumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-15319370.post-30242845023952397742014-05-08T20:03:16.647+02:002014-05-08T20:03:16.647+02:00Hi Sara,
the Javascript step is like a swiss arm...Hi Sara, <br /><br />the Javascript step is like a swiss army knife. It can do anything, but it might not be the most efficient or straightforward tool for the job.<br /><br />To extract the time, you can try a couple of things. You could use a "Select Values" step, and use the "Modify" tab to cast the timstamp values to strings. The fields grid has a "format" column where you can specify the desired format. So there you would type the format string that extracts only the time. Look here for the format strings: http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html<br /><br />I think this format does the job:<br /><br />HH:mm:ss<br /><br />Alternatively you could use the regex step to extract the timepart. <br />rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-66462597547392974262014-05-08T19:17:56.073+02:002014-05-08T19:17:56.073+02:00Hi Roland, I am a begginer with kettle..
I want t...Hi Roland, I am a begginer with kettle..<br /><br />I want to thank you for this awesome tutorial! I just followed your steps to generate my dim_date table. I had also generated a dim_time table.<br /><br />Can you please tell me the script that I should write in the step "Modified Java Script Value" in order to extract only the TIME [09:20:08] from an attribut in a PostgresSQL database (its type is "timestamp without time zone" ) which has this forme " 2014-03-06 09:20:08.742 " ? <br />... in that case the step "Strings cut" isn't useful.<br /><br /><br />Thank you for your help,<br />SaraAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-25330067546536020332013-10-11T18:19:43.983+02:002013-10-11T18:19:43.983+02:00click en get variables button from javascritpt sho...click en get variables button from javascritpt show this: <br />General error executing script:<br />ReferenceError: "initial_date" is not defined.<br /><br />Spoon v. 4.2.0Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-46683101397610528792013-04-11T19:09:29.660+02:002013-04-11T19:09:29.660+02:00Thank you very much for your excelent post! Nevert...Thank you very much for your excelent post! Nevertheless, I think there is a small error on the Javascript part: when you format the 2 digit year, the pattern is "y", and it should be "yy":<br />simpleDateFormat.applyPattern("yy");<br />var year2 = simpleDateFormat.format(date);<br /><br />Hope it helps. Thanks a lot!Roberto Novaeshttps://www.blogger.com/profile/01218552364761017042noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-69520501056365702442010-10-07T00:58:42.028+02:002010-10-07T00:58:42.028+02:00Hi thoughtcrimes!
thanks for the kind words, and...Hi thoughtcrimes! <br /><br />thanks for the kind words, and many thanks for pointing out these bugs. I'm not sure when I'll get round to fixing them, but I can now at least point to your comment if there's someone else having issues.<br /><br />I'm glad this post was useful to you. <br /><br />If you're interested in working with Kettle more, I'd like to point out that recently, the book "Pentaho Kettle Solutions" was published by Wiley and written by me, Jos van Dongen and Matt Casters, chief data integration at Pentaho and Kettle founder and lead developer. You can find it here on amazon: <a href="http://www.amazon.com/Pentaho-Kettle-Solutions-Building-Integration/dp/0470635177" rel="nofollow">http://www.amazon.com/Pentaho-Kettle-Solutions-Building-Integration/dp/0470635177</a>rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-23549561334817187352010-10-06T23:00:42.994+02:002010-10-06T23:00:42.994+02:00Thanks so much for the awesome tip/tutorial! I jus...Thanks so much for the awesome tip/tutorial! I just followed your steps and was able to generate my dim_date table, which will be very helpful. I wanted to point out one minor error in the field names that caused me some problems until I figured it out. In the create table step, you give a column names of is_first_day_in_month and is_last_day_of_month. <br /><br />However, during the modified JS scripting step, you give these output field names of is_first_day_OF_month and is_last_day_OF_month. Running this in Spoon 4.0 and MySQL 5.1 caused the mapping to fail, so the fields were initially populated as 'undefined' instead of yes/no character strings. I changed the name of the columns in the table to match the JS output and it worked fine. Again, thanks for the patient explanation. I feel like I understand both how to create the date dimensions and how to use Kettle better after going through this process.thoughtcrimeshttps://www.blogger.com/profile/01744338026247079437noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-6392330828217587052007-09-26T23:45:00.000+02:002007-09-26T23:45:00.000+02:00Hi Mr.Roland, I am a begginer with kettle. I Have ...Hi Mr.Roland, I am a begginer with kettle. <BR/>I Have a database with a table names "program". On the other hand, I have a colum with a table names "nameofprogram". I need to change the names of programs that start with "ESP" to code "01"; at the same time I need to rename the programs thar start with "ING" to code "02", the other one of names need to be rename with "03" <BR/>all these information need to be save in the same colum and the others colums don´t change.<BR/><BR/>Example:<BR/><BR/>Before:<BR/><BR/>TABLE: PROGRAM<BR/><BR/>nameofprogram colum2 ...<BR/><BR/>ESPECIALIZACIÓN EN GERIATRIA infcolum2 ... <BR/>INGENIERIA MECATRONICA infcolum2 ..<BR/>ESPECIALIZACIÓN EN ODONTOLOGIA infcolum2 ...<BR/>DERECHO<BR/><BR/>After:<BR/><BR/>TABLE: PROGRAM2<BR/><BR/>code .....<BR/><BR/>01 infcolum2 ....<BR/>02 infcolum2 ....<BR/>01 infcolum2 ....<BR/>03 infcolum2 ....<BR/><BR/>How can I do this ? can you send me a transformation file or something like this?<BR/><BR/>Thank you for your help, <BR/>Andrés GarcíaAndrés Garcíahttps://www.blogger.com/profile/16038602406436669171noreply@blogger.com