We would like to send a notification (email) when we get bad data.

[fj40wdh]

We have created a ticket with this question, and they suggested asking this question in the Community.

We would like to send a notification (email) when we get bad data.
Source target Oracle, data type character.
Endpoint target Confluent v7 Kafka Avro primitive.
Background.
We have an older app running that uses Oracle that has date columns defined as char(8). This means users can (and do) enter invalid dates. We transform the character to datetime, and can test using Sqlite for a valid date, but cannot send an email from Sqlite, or from Replicate. When the invalid date is sent to Kafka it shows up as 9999-12-31 23:59 so it's no longer valid. We haven't found an easy way in our data stream to capture the date. We have looked at the user defined function in the expression builder, but currently do not have a C programmer on in our group.
Question
Is there a way to send a notification (email) from Qlik on bad data?

[Heinvandenheuvel]

I don't think you are going to be able to send a message. Best you can do in Replicate is to provide a recognizable valid but bad date. For example:

ifnull(
  date(substr($x,1,4) || '-'  || substr($x,5,2) || '-' || substr($x,7,2)),
'1901-01-01'
)

Next a daily (hourly) job on target to report rows to be analyzed/fixed.

Maybe a trigger on target table to generate an Email?

Maybe you can call a 'fake' target_lookup ( like '1901-01-01' || target_lookup(0,bad_date_seen,'','$x=$pk',$x,$pk) which magically turns into an userfunction running on target if your target (Snowflake) has something like that?

But really, you should fix the source - perhaps with a validation trigger.

Hein.

[fj40wdh]

Thanks.

Oh how we would like to change the source! Vendor app out of our control...

It's looking like a notification out of Replicate is not possible, unless it's possible from a user defined function. But we don't have a C programmer, we couldn't create it or support it.