How to handle Nulls

[revanthga]

Hi Guys,

I had a Issue in my Job.
My source is a sequential File with Id,date,No,
I have to handle nulls with empty spaces & nulls to a separate sequential file.
So i had used a Transformer but mean while i have check the condition as the date should be with in the configurable date specified at runtime.
So i am usinf If Left(Trim(InputCol))='' then '0' else ''1' in stage var & in constraints ihad used this stagevar =0. But here I have to handle Id & Date.
The input file may contain empty spaces & Nulls in these fields.
For e.g
Id,Date,Name,No
123456789,,revanth,123,
,20100101,Gummadi,234,
, ,Revanthg,2323,
While using this I am unable to handle both Nulls & empty chars. If i handle I am getting warning at the constraint level for Configurable date.

Thanks In Advance

[asorrell]

Constraints can be quite complex, allowing you to add for multiple tests. In this case you could keep your old constraints and use AND / OR and parenthesis with additional logic to insure that your date and id also meet requirements.

For example, you could add an additional test to check that your Date field matches your input date guidelines and set a variable called svDateIsBad based on that. You could also add an additional test to validate the id field, and set a variable called svIDIsBad based on that test.

Then your "bad data constraint" would look like this:

Code: Select all

svIsNullField OR svDateIsBad OR svIDIsBad

And your "good data constraint" would look like this:

Code: Select all

NOT(svIsNullField) AND NOT(svDateIsBad) AND NOT(svIDIsBad)

If you want specific help with a specific warning - then post the exact message.

Also - "null" is not the same as "empty string" or "all spaces". Nulls are typically associated with data items being retrieved from a database that contain a specific character which represents "value unknown". In the rare event that a null must be transmitted via a sequential file it is typically assigned a specific value, which can be a unique character or string (ie: ##NULL##) that has been mutually agreed upon by the sender and receiver (there is no "null default" for sequential files).

Just mentioning this because your title talks about "Handling Nulls" - but you don't seem to be doing that.

[revanthga]

If you want specific help with a specific warning - then post the exact message.

My Code is
(StringToDate(Eff_Dt,"%yyyy%mm%dd") > DateFromDaysSince(CONFIG_DT, CurrentDate()))
This is used to load the date with the config date.
If the Eff_Dt is Null Then I am Facing an Error i.e
" Conversion error calling conversion routine date_from_string data may have been lost".
So how to avoid this.
The another Issue is I had a CSV file it contains it contains there also I have to handle Nulls.
Columns are

Code: Select all

CID,SX_ID,STD_CD,,NM,ID(Foreign Key),Eff_Dt
,   ,A,Revanth,1234567890,2010111,
         ,222,R,,20110101,
12345678901,111,C,9876543210,20110202,

I have to handle these Null & Empty Space Records to separate Link.

I used the functions like Len(Trim(Inputcol)) for three keys & in constraints i assigned StageVar=0 Or IsNull(InputCol).
it is able to trim the values which had space & those records are rejecting to null.But I need null records are also to be rejected to separate link.


Thanks in Advance.

[ramsubbiah]

Hi,

check the format of the input value StringToDate(Eff_Dt,"%yyyy%mm%dd") for example %mm% is getting value 15 from source file means wrong because maximum month value is 12, so validate your input,
one more thing for null field column you cannot directly use the transformation functions like TRIM, because before doing the transformation it will simply reject the record, so always do the null handling before use the transformation function
hope this helps otherwise post your exact transformation rule here!

Thanks,
Ram.S

[revanthga]

check the format of the input value StringToDate(Eff_Dt,"%yyyy%mm%dd") for example %mm% is getting value 15 from source file means wrong because maximum month value is 12, so validate your input,
one more thing for null field column you cannot directly use the transformation functions like TRIM, because before doing the transformation it will simply reject the record, so always do the null handling before use the transformation function
hope this helps otherwise post your exact transformation rule here!

How my Data looks like is Specified in the earlier post.
I am using "Len(Trim(InputCol))"as Stage Variable.
In Constraints I am writing it as "StageVar=0 Or IsNull(InputCol)".
But I am unable to handle.
So I have to handle these Null's & Empty chars on Primary key columns to a separate file.

[ray.wurlod]

Let's start again.

How is the Null Field Value property set in your Sequential File stage? Is it set on the Format tab, for individual fields separately, both or neither?

Is a zero length string to be regarded as null? Is a single space to be regarded as null?

Whatever the case, you are going to need to apply a separate test to each field.

If you're going to claim that it's "not working" we're not going to be interested in trying to help. We will be interested if you tell us what you're doing, what you expect to happen, and what actually happens.