topic Re: Null Handling in Talend Studio

Null Handling

Anonymous — Wed, 24 Jul 2019 15:10:18 GMT

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

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

Output:

ID Name Address City Error

1 Century abc Nagpur

2 Name,Address,City column is null

Thanks,

Dua

Re: Null Handling

lennelei — Wed, 24 Jul 2019 15:29:38 GMT

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

Re: Null Handling

Anonymous — Wed, 24 Jul 2019 19:04:32 GMT

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".

Re: Null Handling

Anonymous — Wed, 24 Jul 2019 19:35:04 GMT

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 🙂

Re: Null Handling

lennelei — Thu, 25 Jul 2019 08:10:31 GMT

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.

Re: Null Handling

ThWabi — Thu, 25 Jul 2019 14:30:00 GMT

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

Re: Null Handling

Anonymous — Fri, 26 Jul 2019 13:50:02 GMT

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.

Re: Null Handling

Anonymous — Fri, 26 Jul 2019 19:46:24 GMT

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

Re: Null Handling

Anonymous — Sun, 11 Aug 2019 08:32:49 GMT

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" : ""))))))