idea Re: Handling default type/data conversion of NULL entries from Source (SAP) in Replicate in Suggest an Idea

Handling default type/data conversion of NULL entries from Source (SAP) in Replicate

Bhargav_Ranjit — Mon, 28 Feb 2022 20:09:06 GMT

Qlik Replicate by default does type conversion to a NULL/ BLANK entry from source, for e.g., a NULL entry in Date columns is converted into “00-00-0000”.
Our business wants data in target as is and don’t want to do any conversion.

Need a way to handle it to all tables and columns in the Global-Transformation, as we see many tables/ columns with this behavior?

Re: Handling default type/data conversion of NULL entries from Source (SAP) in Replicate

john_wang — Mon, 28 Feb 2022 23:36:45 GMT

Hello @Bhargav_Ranjit ,

What's the target DB you are using now, and what's the exact data type in source side? it's helpful to understand the behavior.

Regards,

John.

Re: Handling default type/data conversion of NULL entries from Source (SAP) in Replicate

bobvecchione — Tue, 01 Mar 2022 16:22:33 GMT

Hi - Adding to Johns question - is the request only for DATE datatype?

it is very important to understand the targets as john mentioned as the rules for DATE type null handling are very dependent on the database.

Re: Handling default type/data conversion of NULL entries from Source (SAP) in Replicate

Bhargav_Ranjit — Wed, 02 Mar 2022 15:08:40 GMT

Hi John and Bob,

Thanks for your response. The target database is Azure Databricks delta.

We see this behaviour with source datetypes such as 'DATE' & 'INTEGER'.

Cheers,

Bhargav.

Re: Handling default type/data conversion of NULL entries from Source (SAP) in Replicate

john_wang — Thu, 03 Mar 2022 00:51:59 GMT

Hi @Bhargav_Ranjit , copy @bobvecchione ,

There are several key points about the NULL/BLANK convert to 00-00-0000/0 in the end-to-end path (I'm assuming the behavior occurs in both Full Load + CDC. The below steps are focusing on FL. Correct me if I'm inaccurate):

1. The read from SAP source

2. The interim CSV file

3. The data processing/transformation in Replicate

4. The write to Databricks delta files

5. The Hive client display

The behavior may be caused by any one fact in above path, for example maybe all the process in Replicate are correct however the Hive display NULL DATE as 00-00-0000 in the last step. Let's troubleshoot it step by step (corresponding to above points:).

(1) Replace your Databricks delta target by a generic FILE target, what's the result in the target file?

(2) In Databricks delta EndPoint adds two internal parameters and set them to true

keepCSVFiles keepErrorFiles

Re-run the task and check the "***.csv" files under the task folder to see what's the values in interim files

(4) Check the real values in Databricks delta storage files

(5) create a test file and make a test table map to it in Hive then check if Hive client (or other client) interpret NULL value in DATE column to "00-00-0000".

I did not setup an env to reproduce it here. Based on the troubleshooting result then we may figure out if a transformation helps or not.

Only if we confirm there is no way (eg transformation) or no any WA available then it will be a FR item.

Regards,

John.

Re: Handling default type/data conversion of NULL entries from Source (SAP) in Replicate

Bhargav_Ranjit — Wed, 15 Jun 2022 10:18:44 GMT

Hello @john_wang ,

Firstly apologies for delays in responding to your last comment. We were working on ways to handle this situation at our target side - but no success yet due to other restrictions, would be great if we have an enhancement or a transformation option from Qlik to handle Blank/ NULL scenarios,

Below are our findings based on your suggestions,

Testing done so far:

(1) Replace your Databricks delta target by a generic FILE target, what's the result in the target file?

We don’t have license for File type as a target, can Qlik test this in their lab?

(2) We picked couple of tables having blank entries in source and below is the finding

Table Name: VBPA

Column: item_number_of_the_sd_document

Table name: VBAK

Field name: ZZ_VDATU_ORIGINL

(3) Configured databricks endpoint to drop csv, and Re-run the task, checked the "***.csv" files under the task folder to see what's the values in interim files

VBPA Screenshot:

VBAK Screenshot:

(5) Check the real values in Databricks delta storage files

Results in Databricks table (VBPA) & storage files (parquets)

Based on our observations we can see that Delta connector is indeed converting Blank entries in source to a default value for certain data types like NUMCHAR (0) and DATE (0101-01-01).

Request you to provide a solution to to handle the Blank entries in source as Blank in target as well (Databricks for our case) irrespective of data types.

Thanks,

Bhargav.

Re: Handling default type/data conversion of NULL entries from Source (SAP) in Replicate

Bhargav_Ranjit — Mon, 20 Jun 2022 08:48:12 GMT

Hello @john_wang / team,

A gentle reminder, able to check my previous message on this topic?

Re: Handling default type/data conversion of NULL entries from Source (SAP) in Replicate

john_wang — Mon, 20 Jun 2022 12:45:36 GMT

Hello @Bhargav_Ranjit ,

I have not such an env for my test, I'd like suggest you open a support case for further investigation. And you may get a temporary license for your test, please contact your region sales representative, or account manager. Let me know if you need any additional assistance.

copy @bobvecchione ,

thank you,

John.

Re: Handling default type/data conversion of NULL entries from Source (SAP) in Replicate

Bhargav_Ranjit — Mon, 27 Jun 2022 10:28:32 GMT

okay Wang, I requested for File target connector to support- 00025197

Also, checking with our Account Executive- Alicia.Nasrallah@qlik.com

Will keep you posted

From now on, please track this idea from the Ideation por...

Meghann_MacDonald — Wed, 02 Aug 2023 15:34:23 GMT

From now on, please track this idea from the Ideation portal.

Link to new idea

Meghann

NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you only see 1 tab with the login page, please try clicking this link first: Authenticate me! then try the link above again. Ensure pop-up blocker is off.

Re: Handling default type/data conversion of NULL entries from Source (SAP) in Replicate - Status changed to: Closed - Archived

Ideation — Wed, 02 Aug 2023 15:34:25 GMT