Salesforce (Incremental Load) Source Endpoint - Replicating LOOKUP data types as XMLObjects

[slewis]

We are currently replicating 10 history tables (AccountHistory, ContactHistory, OpportunityHistory, etc.) from Salesforce to Snowflake. These History tables track changes applied to related tables (Account, Contact, Opportunity, etc.) and store OLDVALUE and NEW VALUE as LOOKUP data types along with the related column name and related column data type.

When replicated the LOOKUP data types are being written to the target table as XMLObjects.

Example: OpportinityField/OpportinityFieldHistory tables

OpportunityField.Projected_Start_Date       2022-07-01
OpportunityFieldHistory.OldValue                  XmlObject{name={urn:sobject.partner.soap.sforce.com}OldValue, value=Fri Jul 1 18:00:00 MDT 2022, children=[]}

This is undesirable behavior.

Preferred would be for the Salesforce connector to recognize these "history" tables, look at the DataType column in the table, and convert the LOOKUP data type to a string equivalent of that data type so that all data types are transmitted as strings.

Example: OpportinityField/OpportinityFieldHistory tables

OpportunityField.Projected_Start_Date       2022-07-01
OpportunityFieldHistory.OldValue                  "2022-07-01"

Qlik Replicate 

[Michael_Litz · Accepted solution]

Hi Slewis,

While replicate may not recognize these filed types automatically you can achieve a similar goal by using the source lookup function in the transformation screen for a table. You would Add a column to the table defined as string and then in the transformation expression use source lookup to get the field value as string.

Transformation: Source Lookup - Oracle ROWID 5/4 ( Ted )
https://community.qlik.com/t5/Knowledge/Transformation-Source-Lookup-Oracle-ROWID/ta-p/1805170

NOTE: The above example is written for Oracle source and uses =:1 as the parameter where as SQL would use =?

Thanks,
Michael Litz

[slewis]

Michael,

Thank you for your feedback.

I agree that transformations can be used to "convert" the XMLObject values to a more usable form in the Target. However, instead of asking Task designers to guess which table/columns contain LOOKUP data types and need these transformations, the Salesforce endpoint connector should handle this. The LOOKUP data type is specific to Salesforce, just like the COMPOUND FIELDS (addresses and geolocations) data type that are properly handled by the Salesforce connector today.

[slewis]

After looking at the link you provided in your post, there's a second problem. The HISTORY tables in salesforce capture before and after update values for columns. Using the LOOKUP function would only provide the after value. The before value would no longer be available and would require a different transformation.

[Michael_Litz]

Hi Slewis,

Thank you for the update. If these fields are not being handled correctly they you will want to submit and enhancement request to ask if the endpoint can be modified to handle these.

Thank you for submitting this feature request. To submit Feature Requests going forward please use our Qlik Community "Product Insight and Ideas" forum as these requests will no longer be handled as technical support cases. As a Qlik Community member, you can actively engage with our Product Management team, vote on a product idea coming for other Qlik customers, submit your own ideas and get feedback from other members.

To get started please see our article: "Getting Started with Ideas": https://community.qlik.com/t5/Qlik-Product-Insight-Blog/Getting-Started-with-Ideas/ba-p/1684011

You will be required to have a Qlik ID to logon to the Community which is not the same as your support portal logon. If you have previously registered for a Qlik ID such as one you  use to access the downloads site, you can use the same to logon for the Community. First time accessing Community with a Qlik idea will prompt for a username alias to be used when posting to the Community.  This alias is not a logon but for display purposes when posting. If you do not have a Qlik ID for logon, you can register at the logon screen. The Ideas blog post will provide information on how to use the Ideas board and how to access it.

If you have any issues registering or logging on to the Community, please submit a new case with the issue you are having so we can direct the case to our Customer Service team for assistance.  Please let us know if there is anything else we can do for you at this point as this is no longer the place to submit feature requests.

Thank you,
Michael Litz
Customer Support Engineer

[Michael_Litz]

Hi Slewis,

I thought of another thing that may help out here which would be to enable store changes on the task. This will create a __CT table for each base table in the task and that table will have the before and after image in it for each transaction that comes from the source.

Let me know if this helps out?

Thanks,
Michael

[slewis · Accepted solution]

Hi Michael,

Store Changes would be helpful on the "base" table like Accounts or Contacts. The history tables in Salesforce are essentially Store Changes tables in and of themselves. They have OLDVALUE and NEWVALUE columns for that store the before and after state of each column change value applied to a row. Using Store Changes on a history table would result in a OLDVALUE_before, OLDVALUE_after, NEWVALUE_before, and NEWVALUE_after columns and all four would be in XMLObject data type format and difficult to use in analysis.

I will look into and consider the option of NOT replicating the history tables and using Store Changes on the "base" tables to let Qlik Replicate build History tables instead of using Salesforces. That may be a viable work-around.

[Michael_Litz]

Hi Slewis,

Thank you for the feedback. If this approach does work out as a work-around for you please do check this as solution accepted.

Thanks,
Michael