Boolean Values from Salesforce

[raghujakkani]

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_S...

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. 

[Bill_Steinagle]

@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_post...

Thank You!

Bill

[raghujakkani]

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_S...

Thank you,

Raghu. 

[Bill_Steinagle]

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

[Dineshan]

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

[raghujakkani]

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). 

[john_wang · Accepted solution]

Hello @raghujakkani ,

We can manage it by utilize 2 Global Rules:

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

1. Transformation
   Convert data type
2. Transformation Scope
   Schema name is like               : %
   Table name is like                     : %
   Column name is like               : %
   Data type is                                 : BOOLEAN
3. 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')

1. Transformation
   Replace column value
2. Transformation Scope
   Schema name is like               : %
   Table name is like                     : %
   Column name is like                : %
   Data type is                                  : BOOLEAN
3. 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.