topic Calling API in Qlik Replicate for column transformation in Qlik Replicate

Calling API in Qlik Replicate for column transformation

amitw — Wed, 24 Jul 2024 10:13:03 GMT

Hi ,

We have developed a Full + CDC task with SAP ODP endpoint as source and SQL SERVER as target.

We have requirement where we need to transform a value for a specific column from RAW16 format to RAW22 format while loading into target SQL server.

There is an external API developed in HANA which takes in a column value as input and converts the value from RAW16 format to RAW22 format.

Is it possible for Qlik replicate to call an external API in transformation and extract the output value to load into SQL server target?.

Thanks,

Amit

Re: Calling API in Qlik Replicate for column transformation

john_wang — Wed, 24 Jul 2024 12:55:20 GMT

Hello @amitw ,

Thanks for reaching out to Qlik Community!

Qlik Replicate supports rich built-in transformations, and we also support User-defined transformations to extend calling tailor-made user modules, the UDTF should be written in C.

After the UDTF is compiled and setup done, the transformation can be seen in "User Defined" functions list, it take effect for each row in the replication task.

Hope this helps.

John.

Re: Calling API in Qlik Replicate for column transformation

Heinvandenheuvel — Thu, 25 Jul 2024 02:59:02 GMT

Do you perhaps mean RAW16 to CHAR22?

What is the datatype that the RAW16 shows up as in Replicate and/or on the MS SQL target? BYTE(16)?

It's just a large binary number akin to int16 - but that datatype does not exist.

When you print one it is often represented as a HEX string of 32 bytes, which Replicate can transform using the HEX function.

CHAR22 on the other hand is a BASE64 encryption of the same resulting in just 22 printable characters needed. However, there is no function for that build into Replicate.

See: https://www.brandeis.de/en/blog/guid-and-uuid-in-sap-hana

As long as the raw16 does not show up as a blob, it should be possible to create a private function for this and integrate this with Replicate as "User defined" function. There are examples for UDF's provided with the Replicate distribution, and using those I have made some of those in the past for other purposes. Still, this is not a trivial exercise and likely requires the help of Qlik Processional services or other consultancy services.

Good luck,

Hein.