NULL Handling in transformer

[sam334]

Hi all,
Have a question. I searched the forum for this but could not find a definite answer. First, I am using ISNULL IF function which is not working. Job is below.

SEQ--Tranformer--SEQ(.csv)

So, there are 20 columns in input. each column can be NULL or there is a value Datatype, varchar, 0:10:15. Now, in output I dont want NULL instead I want 0:00:00. Function I used is,
IF Input.column1=@NULL THEN 0:00:00 ELSE input.colume1
Also, tried, IF ISNULL(Input.column1) THEN 0:00:00 ELSE input.column1.
Neither is working.

The input and out both column data type is VARCHAR

Any thought.

Thanks.

[chulett]

A couple of things. First, nothing is ever equal to NULL which is why the first check fails. Secondly, there are no true NULL values in a sequential file which is why the second test fails. You should be able to test for an empty string like this assuming we're talking about a delimited file:

IF Input.column1="" THEN "0:00:00" ELSE input.column1

You can also specify the null value property for each field which tells the sequential file reader to substitute a NULL into the column when it sees that null value equivalent. For example, in a fixed-width file if you have an "empty" 8 character field that arrives with 8 spaces in it, specifying 8 spaces as the null value will cause those 8 spaces to be replaced with a NULL during the import.

Edited to add: I'm pretty sure that null value property / attribute is available in Server jobs but it might just be a PX thing.

[sam334]

Thanks Craig.
The input file is actually .csv file. thats why probably "" is not working.

[chulett]

A ".csv file" is a delimited file. So the test I posted isn't working? If so, can you show us some real example records from the file where you have some empty fields you want to be NULL?

[sam334]

Ok.Thanks,
Basically I did a pivot on sql and due to some restrictions i have to put the record in a .csv file.(Input)

Input-
column1,column2,column3,column4,column5.
sam334,NULL,NULL,NULL,07:35:00

Output:-(.csv)
column1,column2,column3,column4,column5.
sam334,0:00:00,0:00:00,0:00:00,07:35:00

So, I want those NULL or Space in input csv file to be 0:00:00 if that particular column is NULL or space else it can take the value such as 07:35:00

[chulett]

Asked for real examples, not really sure that is what we're getting. Are you showing us the fact that the word "NULL" is in the file, those four characters? If not, I'd need to see exactly what the file looks like to provide any more help than I already have.

[sam334]

Craig,
When I view the file, it is showing as NULL not space. I used both IF isnull or column1=" ". so that either one should have taken care of.
I know without seeing the actual file, it is more difficult. Let me try once again.

[ray.wurlod]

... Input.column1=@NULL...

As noted earlier, it is not legal to compare something against NULL.

One should use the IsNull() function to determine whether a field is (contains) null.

In server jobs there exists a system variable (@NULL.STR) that contains a string representation of the internal format of null, which can be used in such comparisons, but best practice is always to use IsNull(), not least because @NULL.STR is not available in parallel jobs.

[ssnegi]

When I view the file, it is showing as NULL not space. I used both IF isnull or column1=" ". so that either one should have taken care of.

Code: Select all

IF Trim(Input.column1) = '' THEN 0:00:00 ELSE input.column1

[ray.wurlod]

How is null actually represented in your Sequential File stage (for example the Null Field Value property on the Format tab or in an extended column definition)?

[sam334]

All,
Solved the issue. I tried different alternatives. The actual issue was,

As there are NULL values in delimited file.This is not space, it actually WORD "NULL". So, If the function is written as,

IF INPUT.COLUMN1="NULL" THEN "0:00:00" ELSE INPUT.COLUMN1, will solve the issue.


Thanks a lot everyone for the input. Appreciate it.

[chulett]

Interesting. Never had anyone send me a file with null values like that.

[qt_ky]

I have found the string "NULL" contained within various source systems (i.e. NAME = "John NULL Doe"). Just one of many surprising data quality issues...