Modify Stage

[rwierdsm]

I've searched the forum to no avail for a way of assigning an outgoing column to zero in a modify stage.

Our mapping rules have changed and a value that is being fed in from a number of jobs will now need to be zero. Rather than modify the 30+ jobs that feed the job in question, I want to set the column to zero in the receiving program. I naively thought, 'Surely the modify stage can do this simple task'!

If such an assignment is possible in the modify stage, it has certainly defied my best attempts at determining how it would be possible.

What I need to do is:
Incoming value: import_seq -> any numeric value
Outgoing value: import_seq -> 0

Has anyone out there successfully performed such an assignment?

(Maybe my question should be 'Has anyone ever actually gotten this stage to do anything useful, other than change a data type?')

Frustrated in Toronto,

Rob W.

[thompsonp]

You want to transform any input value to the value 0 on output. What's wrong with a transformer?

The modify stage does lots of useful things. I suggest you take a look at the tech tips video that shows how to put the modify stage to good use.

[splayer]

The modify stage will drop/keep columns and convert datatypes of columns. Its functionality is pretty rudimentary and that's why it is lot more efficient than a transformer. When you want to change a value of a column, even to 0, you'll need to use a transformer.

[rwierdsm]

thompsonp,

Thanks for your response.

Of course I can use a transformer.

I have used transformers many times when I thought the function I needed to perform should be easy for a modify to accomplish. However transformers are slow. Modify is fast. The occasions I have used it to change data types I have been pleased with the speed with which it performs. I thought that so simple a function as assigning a constant to a column should be managable by a modify and am very surprised that it won't do it in any obvious (to me!) way.

I'm hoping someone out there will tell me I'm being silly and of course a modify can do that -> here's how! Now I need the 'here's how' part....

Rob

[vmcburney]

There is no Modify function that sets a constant value! You can do a little hack to get what you want. For example lookup_int16_from_string( default_value=0('AAA=0')) might return a 0 all the time. NullToZero(SetNull)) might work if the Modify stage accepts multiple functions. IsNull(SourceColumn) might return 0 (for false).

[ray.wurlod]

The Modify stage has a function called handle_null() for replacing an out of band null (the NULL you're talking about) with a specific value (an in-band null).

It also has a function called make_null() for generating an out of band null.

So perhaps you could try:

Code: Select all

NewCol=handle_null(make_null(OldCol),0)

[rwierdsm]

NullToZero(SetNull)) might work if the Modify stage accepts multiple functions.

Thanks for your response Vincent. I tried this particular hack, but no luck!

Rob

[rwierdsm]

Hi Ray,

Got

main_program: Error parsing modify adapter: Error in binding: Encountered more than one 'handle_null' or 'make_null'
Expected destination field selector, got: ")";

on that attempt.

I think I'm going to use the transformer. No more time to play!

Rob

[ray.wurlod]

You might try Vincent's suggestion, but note that the default value clause needs to be enclosed in curly braces and it and the "table" in square brackets.

Code: Select all

lookup_int16_from_string[{default_value=0}('AAA=0')](inputcolumn)