Select and load Salesforce data

QlikView: Select data from Salesforce

Add a LOAD statement

You can add a LOAD statement to your script when you are loading Salesforce data using the Bulk API.

The LOAD statement before the SELECT statement allows the LOAD statement to use the SELECT statements as input. Basically, it is a LOAD statement that loads from the LOAD or SELECT statement below, without specifying a source qualifier, such as From or Resident, as you would normally. You can stack any number of LOAD statements this way. The statement at the bottom will be evaluated first, then the statement above, and so on until the top statement has been evaluated. Another advantage of the LOAD preceding SELECT is that you can keep a calculation in one place and reuse it in LOAD statements placed above. For example, when you load data from a database using a SELECT statement, you cannot use Qlik functions to interpret data in the SELECT statement. The solution is to add a LOAD statement and perform data transformations above the SELECT statement. In this example, string data is interpreted with the Qlik Date# interpretation function in a LOAD statement, using the previous SELECT statement as source:

The LOAD statement created in the script panel is a simple load.

To add the Date# function to the LOAD statement as in the example above, you would edit the script in the script panel.

Change the data selection manually

While the SELECT and LOAD statements are built automatically when you select tables and fields, you can make certain changes to those statements manually. You can make changes to SELECT and LOAD statements in the QlikView Edit Script dialog and the Qlik Sense Data load editor.

In Qlik Sense, field names can be changed in the Fields panel by clicking on the existing name. The changed name is then used as an alias for the original name when the field is loaded.

In QlikView, field names can be changed by defining an alias in the LOAD statement in the Edit Script dialog.

When SELECT statements are generated, they do not include a WHERE clause, but one can be added in the script panel. The statement for the Salesforce Connector supports the addition of WHERE clauses.

When a WHERE clause is used and the selected field uses a date or date-time data type, the date-time format YYYY-MM-DD hh:mm:ss is automatically converted to the SOQL date-time (YYYY-MM-DDThh:mm:ssZ) if ConvertSQLdates is selected in the Salesforce connection dialog. SOQL uses "Z" on the time to indicate that the time is to be read as absolute, with no time zone offset. In SOQL, “Z” can be substituted with an offset value. For example, the date “2009-02-24 23:01:01” in Eastern US time zone would be “2009-02-24T23:01:01-05:00” in SOQL. Because the YYYY-MM-DD hh:mm:ss format has no offset value, the date-time is converted to Coordinated Universal Time (UTC).

The SELECT statement for the Salesforce Connector also supports:

• SELECT * statement: The Salesforce Connector allows you to use the SELECT * statement to select all fields in the table. You add the SELECT * statement manually in the script panel.

  The SOQL query language does not support the SELECT * statement, but the Salesforce Connector has been enhanced to recognize that syntax. Only asterisks (*) and simple field names can be used in the SELECT * statement.

  The SELECT * statement can only be used when Bulk API is selected in the Salesforce connection dialog.

• String escape characters: The Salesforce Connector uses the single quotation mark character as an escape character instead of the back slash (\) used by SOQL. To include a single quotation mark in a query string, it must be prefaced by another single quotation mark. The Salesforce Connector uses the single quotation mark character as an escape character instead of the back slash (\) used by SOQL. To include a single quotation mark in a query string, it must be prefaced by another single quotation mark. For example, the following string encloses Captain Awesome in single quotation marks:

  Devon ''Captain Awesome'' Woodcomb

The SELECT statement for the Salesforce Connector does not support:

• Qualified field names: Field names cannot be prefixed by object names. For example, “Contact.FirstName” is not permitted.
• Salesforce prefix: It is not permissible to prefix the table name with the string “Salesforce.” For example, “Salesforce.Contact” is not allowed.

• Bracketed names: table and field names cannot be put in brackets. For example, the following SELECT statement is not valid:

  SELECT [FirstName] FROM [Contact]

• SOQL aliases: The syntax for creating aliases in SOQL cannot be used. For example, the following SOQL syntax is not valid for the Salesforce Connector:

  SELECT Id BusinessID FROM Contact

Load Salesforce objects with relationship queries

SELECT statements can be written to load Salesforce objects with relationship queries. To create a relationship query, you must add it manually, which requires that you use the Edit Script dialog or the Data load editor. Relationship query statements must be entered directly in the script panel. They cannot be created simply by selecting multiple tables and fields from the Objects/Tables and Fields lists.

In the following example, the SELECT statement includes fields from multiple tables.

SELECT Name,

(SELECT Name FROM Contacts), (SELECT Subject, CaseNumber FROM Cases)

FROM Account;

From this SELECT statement, the connector creates separate load statements for each of the related tables/objects.

The resulting load produces three related tables.