8 Replies Latest reply on Nov 1, 2020 1:41 AM by Sathiesh M

Apply Null value is not working

Pavithra R Oct 21, 2020 7:55 AM

Hi All,

We have requirement,where a Null update should not get updated from some Non Null value for Source ABC.

1.If I receive Null value on Day 1,it should be accepted.

2.If I receive Non Null on day 1,it should get accepted but on Day2 if I receive null in place of some value,existing value should not be overridden.

We have 2 xref contribution for the record and we have unchecked the check box of staging table property 'Allow Null Update'. But the Null value is still getting updated.

Please help if anything else need to be done on this.

Thanks

1. Re: Apply Null value is not working

kunal pandit Oct 21, 2020 10:25 AM (in response to Pavithra R)

Hi Pavithra,

Try introducing a validation rule that degrades the Trust of a NULL value. In this scenario, a Not NULL value will end with higher trust and WIN.

thanks
Kunal
Like Show 0 Likes(0)

Actions
2. Re: Apply Null value is not working

Pavithra R Oct 22, 2020 7:36 AM (in response to kunal pandit)

Thanks Kunal for the update,

Here in my case,Only Source ABC is the contributor for the column and sometime we receive Null also.

As per my understanding,Validation and downgrade comes,when we receive values from multiple sources.
Like Show 0 Likes(0)

Actions
3. Re: Apply Null value is not working

Tushar Chaugule Oct 22, 2020 7:59 AM (in response to Pavithra R)
Hello Pavitha,

Can you check the configuration on BO -> Columns -> Apply Null Values
Is it enabled ? If yes, uncheck that.

Also MDM follows below behavior,

A process ignores both the Allow Null Update property and the Apply Null Values property in the following scenarios:
When all sources contribute values that are not null, the value from the most trusted source survives.
When all sources contribute null values, the null value survives.
When a base object has a single source system, the value—null or not null—from that source is written to the base object.

Thanks,
Tushar
Like Show 0 Likes(0)

Actions
4. Re: Apply Null value is not working

danantha Oct 22, 2020 10:22 AM (in response to Pavithra R)

If your requirement is that non-null values should not get updated to a 'null' value, then that is the default behavior. Unchecking 'allow null updates' is sufficient for future loads.

If the data already exists and was loaded with 'allow null updates' , then you will have to check the null_indicator_bitmap

Please check KB 140500 to understand null_indicator_bitmap.
KB 512657 to understand how 'allow null updates' and 'apply null values' work.
Like Show 0 Likes(0)

Actions
5. Re: Apply Null value is not working

Pavithra R Oct 22, 2020 11:13 PM (in response to Tushar Chaugule)

Hi Tushar,

We have Apply Null values enabled at BO level,because is we receive Null in initial load,that record should get loaded.

The requirement is only Null update during incremental load should not get overridden over Non null value.
Like Show 0 Likes(0)

Actions
6. Re: Apply Null value is not working

kunal pandit Oct 27, 2020 12:18 AM (in response to Pavithra R)

Pavithra,

Have you considered introducing the validation rule after your Initial Data Load is completed, will it not help with your scenario?

thanks
Kunal
Like Show 0 Likes(0)

Actions
7. Re: Apply Null value is not working

Piyush Paras Oct 29, 2020 12:06 PM (in response to Pavithra R)

Please refer to the KB article: https://knowledge.informatica.com/s/article/512657?internal=1&myk=apply+null+vs+allow+null
Like Show 0 Likes(0)

Actions
8. Re: Apply Null value is not working

Sathiesh M Nov 1, 2020 1:41 AM (in response to Pavithra R)

I assume, in addition to BO, you dont want the XREF record also to be updated with NULL for that column. If thats the case, none of the settings will help you, as they work only on BO. Informatica blindly accepts values as-is for XREF from the source.

Now you've 2 options,

1. If you think source is correct in sending NULL update, but you dont want to consume that in MDM, then simply add a default value to that column

2. But assuming source made a bad update to NULL, and you want to retain old value, then in data cleansing layer replace the column value in STAGE table to previous value from XREF. One way of achieving this is in ETL use a SourceQualifier join on XREF table and get the previous value to pass on to STAGE target.
Like Show 0 Likes(0)

Actions