topic Re: Boolean Values from Salesforce in Qlik Replicate

Boolean Values from Salesforce

raghujakkani — Tue, 07 Mar 2023 16:47:17 GMT

Hello,

I am using Salesforce as Source and Oracle Database as target. I am doing a Full Load method using Bulk API.

Boolean values are returning as "1" or "0" at the Target side (Oracle Database) , they are suppose to be return as "True" or "False".

Found this article : https://help.qlik.com/en-US/cloud-services/Subsystems/Salesforce_Connector_help/Content/Connectors_Salesforce/Create-Salesforce-connection/Create-salesforce-connection.htm

It says to select the Boolean as Strings , Is that something I have to configure at Qlik side or is it to be configured on Salesforce end ?

Thank you,

Raghu.

Re: Boolean Values from Salesforce

Bill_Steinagle — Tue, 07 Mar 2023 18:13:28 GMT

@raghujakkani

Hello Thank you for your post to the QDI Forums. Can you check as the link below Replicate maps the BOOLEN column to BOOLEN as per the documentation. The one posted is for the QDCI Cloud product as not sure which one you are posting. See link below and let us know if this helps.

https://help.qlik.com/en-US/replicate/November2022/Content/Replicate/Main/salesforce/salesforce_postgresql_db_data_types_target.htm

Thank You!

Bill

Re: Boolean Values from Salesforce

raghujakkani — Tue, 07 Mar 2023 18:40:19 GMT

Hello @Bill_Steinagle

I am using Qlik Replicate for Data Replication from Salesforce to Oracle. The document shows the Boolean data type is supported on both ends. i.e, Salesforce and Qlik.

But the below document describes our Issue clearly, BOOLEAN Values returning "1" or "0" if it is not selected. I wonder if the selection has to be done on Qlik Replicate ?

https://help.qlik.com/en-US/cloud-services/Subsystems/Salesforce_Connector_help/Content/Connectors_Salesforce/Create-Salesforce-connection/Create-salesforce-connection.htm#anchor-3

Thank you,

Raghu.

Re: Boolean Values from Salesforce

Bill_Steinagle — Tue, 07 Mar 2023 18:44:10 GMT

Hi @raghujakkani

If there is an issue with the Task with the Boolen Datatype please open a Support case as we can troubleshoot further as needed. When you open the case share the Mapping of the Source and Target from the transformation from Replicate as per the Article we can validate.

Thanks!

Bill

Re: Boolean Values from Salesforce

Dineshan — Tue, 07 Mar 2023 18:52:21 GMT

Hello @raghujakkani,

From Replicate GUI could you please set the BOOLEAN columns in target table as STRING(1)? This could be a workaround. If that doesn't help, then please open a support ticket as Bill suggested.

Thank you,

Dinesh

Re: Boolean Values from Salesforce

raghujakkani — Tue, 07 Mar 2023 22:34:46 GMT

Hello @Dineshan

The Actual data type in Qlik is showing as BOOLEAN which is good but when comes to Oracle it shows the data types as NUMBER(1)

Tried changing the BOOLEAN to STRING(50) in replicate GUI but no luck, Still see the values as "1" or "0". Oracle shows the data type as VARCHAR2 (50 Byte).

Re: Boolean Values from Salesforce

john_wang — Wed, 08 Mar 2023 14:04:37 GMT

Hello @raghujakkani ,

We can manage it by utilize 2 Global Rules:

Convert data type (i.e. convert BOOLEAN to STRING(10))

Transformation
Convert data type
Transformation Scope
Schema name is like : %
Table name is like : %
Column name is like : %
Data type is : BOOLEAN
Transformation Action
Set target data type to : STRING(10)
Set target sub data type to : Regular

Convert column value (i.e. replace Boolean 1/0 by 'TRUE'/'FALSE')

Transformation
Replace column value
Transformation Scope
Schema name is like : %
Table name is like : %
Column name is like : %
Data type is : BOOLEAN
Transformation Action
Column data type : STRING(10)
Value : CASE $AR_M_SOURCE_COLUMN_DATA WHEN 1 THEN 'TRUE' ELSE 'FALSE' END
Column sub data type : Regular

Hope this helps.

Regards,

John.