Deriving value for a new column using a Modify stage

[sandy]

Hi All,

I need some help on the usage of modify stage. Let me first explain the scenario.

We have two fields in the input say F1 and F2. We want a third field F3 in the output(in addition to F1 and F2) whose value is decided as follows :

F3 = F1 if F1 is not null and
F3 = NULL if F1 is null.

The above operation can be done in a transformer but we want to avoid using a transformer in the job.
So we were trying to use a combination of filter and modify stages. We are filtering the null records and using a modify stage for the filtered and the rejected records(from the filter stage)we are adding the third field F3. Finally using a funnel stage, we are collecting the records into a sequential file.

The modify stage has the following specification :

KEEP F1,F2
SetNull(F3:string)

When we are trying to do a validate run for this job, it fails. Can somebody guide me on how to use SetNull function to assign field F3 a value of null? But first, can we use a modify stage to define a new column, which is not present in the input, and derive a value for it?


Thanks in Advance.

[richdhan]

Hi Sandy,

Iam quite confused by this

F3 = F1 if F1 is not null and
F3 = NULL if F1 is null.

Yes, you can have a new field and derive a derivation for that
You have to use new_field=handle_null(old_field, value) in the Specification of the modify stage.

HTH
--Rich

Pride comes before a fall
Humility comes before honour

[sandy]

Hi Rich,

Let me clear the confusion.

Field F3 should be NULL if field F1 is null else it should be equal to field F1.

Hope this clears the confusion.

Also note that field F3 is not present in the input of the modify stage but I want it to be present in the output of it. Can this be done?

Your answer is to handle a null value coming in a column, but I want to set a column value to null no matter what the incoming data is. Any suggestions?

Thanks for your reply.

[bobyon]

Forgive me if I am missing the obvious, but is there some reason

F3 = F1

won't work?

[sandy]

Bob you are exactly right. I want F3 = F1 but without using a transformer stage. I tried using a copy stage to map F1 into F3 but got "Invalid derivation for output column F1." compilation error in return. So I was trying to use a modify stage. Now the modify stage has 2 input columns and 3 output columns and the 3'rd output column F3=F1. Looks simple, but this job is failing a validate run.

The specification in the modify stage is as follows :
Specification : F3=F1
Specification : KEEP F1,F2,F3

The fatal error on validate run with the above specification is
Copy_of_Sequential_File_0: Error when checking operator: Could not find input field "Field1"

Here "Copy_of_Sequential_File_0:" is the final sequential file stage of the job.

Any idea where I may be going wrong!!!!

Thanks.

[Kirtikumar]

The SetNULL() function can be used as it is i.e. only SetNULL() no arguments nothing