PDA

View Full Version : How to insert a constant value into a Table output.



kettle_anonymous
12-29-2005, 12:04 PM
Hi,


I am having some problems trying to figure out how to define a Constant to be used for populating a field within a Table output.



I have a Text File Input step which connects to a Select Values step which connects to a Table output step. The Text file input contains 9 fields and the Table output contains 10 fields. I wish to move the 9 fields from the file and create one Constant value for the table output's 10th field. How do I create this constant value to be inserted into the table output? How do I define the Select Values so that the table does not try to remove its 10th column? Is there a better way to do these simple translations?



Thanks,



Jason Toyne

MattCasters
12-29-2005, 12:19 PM
Possible solutions:

1) Add a Calculator step (Transform category)
Use Calculation: "set field to constant value A"
Specify metadata, standard conversion rules apply:
Date: yyyy/MM/dd HH:mm:ss.ppp
Boolean: Y/N, True/False

2) Use a javascript piece of code: (Java Script Value step under Transform category)

var constantValue = "A string";

3) Generate 1 row of data with Row Generator (Input category) and use a Join Rows (in Transform Category)
(Don't specify a condition)

Good luck!

Matt

JasonToyne
12-29-2005, 01:04 PM
Matt,

Thanks for the quick response.

When I try to use either the calculator or the javascript I run into a problem because I need to have input values for these steps. Which for this constant field I do not have. Also, there are a few other fields which I don't want to insert at all into the output table. In order to cause the output table not to remove these fields I need to specifiy them somewhere. Any ideas?

Jason Toyne

MattCasters
12-29-2005, 10:54 PM
Hi Jason,

The whole idea behind Kettle is to leave rows as they are as much as possible.
This means that you just give all fields as input to Calculator.
Then just one constant value is added to the rows. It's as simple as that.
It gives the best performance if you really think about it. (no assembly of fields)

The whole "Constant" idea of for example Oracle Warehouse Builder is based on programming models like PL/SQL. No program model is used in Kettle, it's all meta-data.

Select values/fields using ... the "Select values" step in the Transform Category.

Basically you have the following steps in your example (as far as I know):
1) Text File Input : read fields from a text file
2) Calculator : add a constant value
3) Select values: select the values you want
4) Table Output: stores the data in a table

You could add database lookup steps etc in between, but unless you do special things, it's all serial: one step linked to the next.

I hope this helps you out.

Good luck!

Matt

JasonToyne
12-30-2005, 05:16 AM
Hi Matt,

Thanks for the explanation. I tried it and believe it will work. However, I have ran into another item I don't understand. I have a Text File Input --> Select Values --> Calculator --> Table Output. If the Text File Input contains any fields that have null/no values then the Select Values step throws an exception. Is there a way around this as our Input files will often have null values.

Thanks again,

Jason Toyne

MattCasters
12-30-2005, 05:44 AM
Hi Jason,

Null values shouldn't be a problem and if an Exception occurs then it should be treated as a bug.
I haven't had it before and almost every transformation uses it, but that doesn't mean anything, the possibilities to check for are almost endless.

Can you tell me a bit about the data type that's throwing the exception?
Perhaps some debugging output?

BTW, if there are no values in the row (empty row) then that's a whole different story.
You can check 'No empty rows' in the Text file input step to avoid those.

Thanks,

Matt

mleibel
05-22-2009, 11:40 AM
I used this to set processed flag after each row is inserted. Works great.

Thanks:p