Null Handling

[Anonymous]

Hello,

My requirement is check null in multiple columns,if null found in any column then mention the reason in error column.Do i need to write java code for that, or I can do it in tmap itself?

Kindly suggest.

For example:

Source:

ID Name    Address      City

 1  Century   abc         Nagpur

 2 

***********************************

Output:

ID Name    Address      City      Error

 1  Century   abc         Nagpur

 2                                               Name,Address,City column is null

Thanks,

Dua

[lennelei]

Hi,

you may use a tSchemaComplianceCheck component : if you untick the Nullable checkbox of the columns you want to check in the schema, it will reject rows will null value with a message "column_name1:empty or null; column_name2:empty or null" if column_name1 and column_name2 are null for example.

Don't forget to uncheck the Treat all empty string as NULL option in the Advanced Settings if you want to check only for nulls (if you don't, the component will also reject empty strings).

Regards

[Anonymous]

Hi Lennelei,
Thanks for the reply.
If I use tschemacompliance check component it will give me 2 flow main and reject, But what I want is if column(name) is coming null from source..still I have to load null into the target table and in target table I have one column(err_text) in that I have to put "name is null".

[Anonymous]

Hi,

    If its the requirment, you can do the null check by Relational ISNULL function and if the value is true, you can create a message for each column and if its not null, add an empty string error message. While loading the data, merge all the error messages by concatenation operator (+) and you can write the output to error column.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[lennelei]

Hi,

You can redirect both outputs of the tSchemaComplianceCheck to the database. If you don't insert more than a few thousands rows at a time, it shouldn't lock the whole table and you should be fine. I presume that depends on the database of course.

In the worst scenario, save the rows from both outputs either in memory (tHashOutput) or in a temporary file and then insert the data from there.

If that doesn't answer your needs, you'll have to write the whole expression manually in a tMap for example.

Regards.

[ThWabi · Accepted solution]

Hello Dua,

the code in tMap for Var.Error could be

Source.Name == null || Source.Address == null || Source.City == null ? "Name,Address,City column is null" : ""

Best regards,

Thomas

[Anonymous]

Hi Dua,

With tMap this can be easily solved. 

First check null condition of each column using [Relational.ISNULL(row2.Name)?"":row2.Name ] 

Second step is to create one variable and give the below condition 

Then you will get the following output.

[Anonymous]

Hi Hesinberg,
Thanks for the reply 😊
The solution provide by you is working fine if each column contain null value.But if I have source like this:
Name. address
Hesinberg Null
Output:
Name address Error_text
Hesinberg. Null. Name,address is null
It will give message like name,address is null because you are hardcoding every column in else condition.

Thanks,
Dua

[Anonymous]

Hi Dua,

Then, in this scenario, you have to take all the possible case to print which column is NULL.

Here is a full condition : -

Relational.ISNULL(row2.Name) && Relational.ISNULL(row2.Address) && Relational.ISNULL(row2.City) ? "Name, Address, City is NULL" : (Relational.ISNULL(row2.Name) && Relational.ISNULL(row2.Address) && !Relational.ISNULL(row2.City) ? "Name, Address is NULL" : (Relational.ISNULL(row2.Name) && !Relational.ISNULL(row2.Address) && Relational.ISNULL(row2.City) ? "Name, City is NULL" : (!Relational.ISNULL(row2.Name) && Relational.ISNULL(row2.City) && Relational.ISNULL(row2.Address) ? "Address, City is NULL" : (!Relational.ISNULL(row2.Name) && !Relational.ISNULL(row2.Address) && Relational.ISNULL(row2.City) ? "City is NULL" : (!Relational.ISNULL(row2.Name) && Relational.ISNULL(row2.Address) && !Relational.ISNULL(row2.City) ? "Address is NULL" : (Relational.ISNULL(row2.Name) && !Relational.ISNULL(row2.Address) && !Relational.ISNULL(row2.City) ? "Name is NULL" : ""))))))