tag:blogger.com,1999:blog-15319370.post4441048954144292400..comments2018-05-22T15:45:55.198+02:00Comments on Roland Bouman's blog: Substituting variables in Kettle Parameter valuesRoland Boumanhttps://plus.google.com/107193837009558767600noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-15319370.post-44368744271113199942013-05-29T09:27:44.555+02:002013-05-29T09:27:44.555+02:00Hi Unknown,
Your question does not relate to thi...Hi Unknown, <br /><br />Your question does not relate to this blog post. Please use general forums and community support next time. <br /><br />As for your question:<br /><br />&quot;I a job that run sql statement&quot;<br /><br />Are you using the SQL job entry? I don&#39;t think that works if you want to use the resultset. Instead, you should build a transformation that uses a &quot;table input&quot; step (in the input category), and then use a &quot;copy rows to result&quot; step (in the job category) to export the result to the job containing the transformation. <br /><br />Then, inside the job, you can right click on the receiving transformation and check the &quot;run for each row&quot; flag. To get the values into the receiving transformation, you can either use parameters or a &quot;get rows from result&quot; input step. In the case of parameters, double click the transformation to open its configuration dialog, and use the parameters tab to configure how fields from the resultset map to the parameters of the receiving transformation.<br /><br />The following post may also be useful:<br /><br />http://type-exit.org/adventures-with-open-source-bi/2010/06/run-kettle-job-for-each-row/<br /><br />Roland Boumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-58600043196529632322013-05-29T04:18:07.591+02:002013-05-29T04:18:07.591+02:00Hi Roland
I&#39;m having difficulties passing val...Hi Roland <br />I&#39;m having difficulties passing values in to transformation. I a job that run sql statement that generate multiple rows and I want run next transformation for each row. I can&#39;t seems to get values inside the transformation. Could you point me in the right direction. I&#39;m on 4.4 stable. ThanksUnknownhttps://www.blogger.com/profile/10867164671060494725noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-74351582162748032192013-05-29T04:16:36.029+02:002013-05-29T04:16:36.029+02:00Hi Roland
I&#39;m having difficulties passing val...Hi Roland <br />I&#39;m having difficulties passing values in to transformation. I a job that run sql statement that generate multiple rows and I want run next transformation for each row. I can&#39;t seems to get values inside the transformation. Could you point me in the right direction. I&#39;m on 4.4 stable. ThanksUnknownhttps://www.blogger.com/profile/10867164671060494725noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-53100748466947510232012-11-19T18:07:05.829+01:002012-11-19T18:07:05.829+01:00Thanks, Roland.
I&#39;m trying to load data from ...Thanks, Roland.<br /><br />I&#39;m trying to load data from spreadsheets. Some of the column values a user would enter may be entered many many times. To alleviate this, I wanted to allow a configurable default setting on the job as a variable (dfltval from ${DEFAULT_VAL}), and allow the user to simply set a block of data in the spreadsheet by setting the value in the first row of the block (currVal). This value would need to be preserved and used across rows (prevval) until a new block is started. Whenever a new block is entered, update the value for the block (prevval = currval). <br /><br />When I get the the output of the step, I wanted to just use the value from the JS step (useval), and leave the logic in just that step.Karl Millerhttps://www.blogger.com/profile/17564814335089410373noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-77127694781377721112012-11-17T08:50:45.973+01:002012-11-17T08:50:45.973+01:00Hi Karl,
Just based on a general scan of the cod...Hi Karl, <br /><br />Just based on a general scan of the code and the comments, I&#39;d say this:<br /><br />// Use the previous value for the current row.<br />currval = prevval;<br /><br />is not correct. That is, I&#39;d expect it to be<br /><br />useval = prevval;<br /><br />As a more general comment on what you&#39;re trying to achieve:<br /><br />&quot;And then want my output to use the value of FIELD_01 and have the next record see the value of PREV_01 in case it needs it.&quot;<br /><br />This is not really how Kettle variables were meant to be used. Variable references (like ${VARIABLE_NAME}) in configuration dialogs are resolved once in the init phase of a transformation / job. So while you can change the value of the variable with javascript or with the set variables step many times, this typically doesn&#39;t change anything since the variables were read out once already (although I expect dynamically reading out the variables with javascript would work). <br /><br />The typical way that variables are used is that in a job, there is a transformation or set variables job entry that sets the value of the variables once, and then the subsequent job entries resolve their variable references against that.<br /><br />There&#39;s more that could be said about your code snippet, but this may be superceded by the remark above. <br /><br />Can you tell us abit about what you want to achieve by setting the variables dynamically. Chances are your problem can be solved in another way. Roland Boumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-76488501365781250602012-11-16T23:30:08.547+01:002012-11-16T23:30:08.547+01:00Roland,
I&#39;m trying to run a transform wit...Roland,<br /> I&#39;m trying to run a transform with 4.1 where I need to do something like the following (I&#39;m a novice with JS, so bear with, please):<br /><br />// If the current value is not null,<br />if (!(currval.isNull))<br />{<br /> // Use the current vale in this record.<br /> useval = currval;<br /> // Save the current value for the next record&#39;s previous value<br /> prevval = currval;<br />} <br />// If the current value is NULL and a PREVIOUS value exists,<br />else if ((currval.isNull) &amp;&amp; (!(prevval.isNull)))<br />{<br /> // Use the previous value for the current row.<br /> currval = prevval;<br />} <br />// If there&#39;s no current value or previous value (e.g. first record has a blank), <br />else if ((currval.isNull) &amp; (prevval.isNull))<br />{<br /> // Use the DEFAULT value for both this run and preserve it for the next row, just in case.<br /> useval = dfltval;<br /> prevval = dfltval;<br />}<br /><br />setVariable(FIELD_01,useval);<br />setVariable(PREV_01, prevval);<br /><br />And then want my output to use the value of FIELD_01 and have the next record see the value of PREV_01 in case it needs it.<br /><br />How can I accomplish this?<br /><br />Thanks,<br />KarlKarl Millerhttps://www.blogger.com/profile/17564814335089410373noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-41393979618663876692011-11-30T17:38:29.379+01:002011-11-30T17:38:29.379+01:00Hi Joao Luis,
please look in the samples/transfor...Hi Joao Luis,<br /><br />please look in the samples/transformations directory underneath your kettle installation dir. You&#39;ll find lots of samples there, including one called &quot;Calculator.ktr&quot;.Roland Boumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-81851130364843610082011-11-30T14:03:51.014+01:002011-11-30T14:03:51.014+01:00Hi Roland
I&#39;m sort of a rookie using Kettle 4...Hi Roland<br /><br />I&#39;m sort of a rookie using Kettle 4.1.0 and I&#39;m having problems in getting variables values with Calculator.<br /><br />Can you please provide an example that works?<br /><br />Thanks in advance for your help.Joao Luisnoreply@blogger.com