topic Re: Data enrichment transform function for generating RRN for target table in Qlik Replicate

Data enrichment transform function for generating RRN for target table

sundar_r — Mon, 05 Sep 2022 23:48:30 GMT

Hi,

I am trying to set a data enrichment transform function(target_lookup) for generating RRN. My situation demands generating RRN since my source table will be truncated and reloaded everyday and target table should continue with RRN value max(RRN) + 1 of the target table.

I am trying to use the below data enrichment function but getting the mentioned error:

case when target_lookup(0,"AWSTEST","PAYHISTDAY","max('RRN')",'','')==NULL
then 1
else target_lookup(0,"AWSTEST","PAYHISTDAY","max('RRN')",'','')+1
end

Error:

Command failed to load data with exit error code 1, Command output: ERROR: null value in column "RRN" violates not-null constraint

Am I missing anything here, any help for resolving the error would be much appreciated.

Thanks,

Sundar Raman

Re: Data enrichment transform function for generating RRN for target table

Heinvandenheuvel — Tue, 06 Sep 2022 01:56:04 GMT

I think you have your quotes wrong.

While the target database (you fail to indicate which one) may like double quotes around object, the Replicate documentation clearly indicates using single quotes to pass object names and expressions as strings to it.

I would NOT quote the column name in the MAX function. That's target SQL and normally does not need a quoted column name unless perhaps there is an uppercase/lowercase issue?

If it still fails, you should try to get the exact sql statement as requested either from the target database SQL trace, or from the reptask log in verbose mode. Of course you'd try this in DEV with a test table with a single, or no more than a handful of rows.

Finally I strongly encourage you NOT to use target_lookup but use a target DB computed value or trigger. Doing ( 2 per row) target lookups slows down Replicate a lot and is not even allowed on some (high performance, CSV fed) DB's such as Snowflake. Just do NOT replicate the RRN column and create a default value formula?

Good luck,

Hein.

Re: Data enrichment transform function for generating RRN for target table

sundar_r — Tue, 06 Sep 2022 19:29:58 GMT

Thanks Hein for the reply, my source table is DB2 and target is PostgreSQL. Looking at the verbose trace it looks like column name does require double quotes, removing them it throws an error. I tried creating a new target table with RRN column allowing null values and removing the primary key constraint, strangely it still throws the same error saying: null value in column "RRN" violates not-null constraint

Re: Data enrichment transform function for generating RRN for target table

Heinvandenheuvel — Tue, 06 Sep 2022 23:49:53 GMT

>> Looking at the verbose trace it looks like column name does require double quotes

Show us.

Re: Data enrichment transform function for generating RRN for target table

john_wang — Tue, 13 Sep 2022 10:08:40 GMT

Hello @sundar_r , copy @Heinvandenheuvel ,

For NULL keyword the operator should be "is" rather than "==". Please use Replicate Expression Builder to make sure the expression is accurate.

Hope this helps.

Regards,

John.

Re: Data enrichment transform function for generating RRN for target table

Lohore — Thu, 29 Dec 2022 12:24:39 GMT

I wonder if your suggestions helped. As far as my knowledge goes, the issues described occur due to unoptimized functions in the table/CRM. Manual data enrichment is a time-consuming process that requires you to play detective, scouring company websites, Google, and individual LinkedIn and social media profiles. However, automated lead enrichment is a real salvation for businesses involved in the sales sector.

Re: Data enrichment transform function for generating RRN for target table

john_wang — Tue, 27 Dec 2022 12:52:57 GMT

Hello @Lohore ,

Please let us know the source/target DBs type and what's the exact usage, a usage sample is much helpful. We'd like to have more investigation for you.

Regards,

John.