Using OData Connector with OData v4.0 JSON Messages with Qlik Web Connectors (Qlik Cloud)

Sonja_Bauernfeind — Tue, 10 May 2022 19:14:14 GMT

Qlik Web Connectors support OData v4.0 and responses in JSON format.

This article documents a how-to scenario for Qlik Cloud. For an example using the Standalone Qlik Web Connectors, see Using OData Connector with OData v4.0 JSON Messages with Standalone Qlik Web Connectors.

Environment:

Real-World Scenario

Find the total revenue of each product from all orders using the example OData service: https://services.odata.org/V4/Northwind/Northwind.svc

Create the OData Connection

From the Data Load Editor, create a new connection and select OData
In the Create new connection dialog, provide the OData Service Root (https://services.odata.org/V4/Northwind/Northwind.svc)
Name the connection and click Create

Select Tables from the OData Source

Using the JsonV4ListResources Table

The JsonV4ListResources table will return the available resources from your OData service.

In the Data Load Editor, select the OData connection you created above and click the Select Data button
On the Select data to load dialog select the JsonV4ListResources table and click the Preview data button

You can use the values returned in the name column from the JsonV4ListResources table as the input to the OData Resource Path parameter on the JsonV4GetData table. For this example, we want to use the Orders resource.

Using the JsonV4GetData Table

The JsonV4GetData table will return the data from the resource specified in the OData Resource Path parameter

On the Select data to load dialog select the JsonV4GetData table
Enter the value from the name column from the JsonV4ListResources table (Orders) into the OData Resource Path parameter and click the Preview data button

Note that collection valued properties are not returned by this table. A placeholder with the value “[Collection]” is returned instead. That data can then be retrieved by using the JsonV4GetDataCollection table. In cases where the collection values properties are also navigation properties, the placeholder that is returned is “[Collection]*”.

In this example, we want to obtain the data in the Order_Details collection for each order so we can calculate the total revenue by product. To do this we must use the metadata returned from the JsonV4GetData table and identify the following columns to be used as parameter values in the JsonV4GetDataCollection table:
- the OData ID column name – in this case, OrderID
- the OData collection column name – in this case, Order_Details

Using the JsonV4GetDataCollection Table

The JsonV4GetDataCollection table will return the data from the OData collection specified in the OData collection column name parameter.

On the Select data to load dialog check the JsonV4GetDataCollection table
Enter the value from the name column from the JsonV4ListResources table (Orders) into the OData Resource Path parameter
Enter the column name from the JsonV4GetData table that will be used as the ID for the resulting table (OrderID) into the OData ID column name parameter
Enter the column name from the JsonV4GetData table of the collection you wish to get data from (Order_Details) into the OData collection column name parameter
Since Order_Details is a navigation property, as noted by the “[Collection]*” placeholder, you will need to specify the value $expand=Order_Details in the OData Query Options parameter to retrieve the data.
Click the Preview data button

For this example, we also wish to expand the Product navigation property within the Order_Details collection to return the actual product name. To do this you will need to nest the $expand options and set the value of the OData Query Options parameter to $expand=Order_Details($expand=Product).
Click the Insert Script button, then Load Data from the Data Load Editor

Visualize the OData data

Build a new sheet in your app
Add a Bar Chart to the sheet
Add a new Dimension to your Bar Chart using the following field:
JsonV4GetDataCollection.Order_Details_Product_ProductName
Add a new Measure to you Bar Chart with the following expression:
Sum([JsonV4GetDataCollection.Order_Details_Quantity] * [JsonV4GetDataCollection.Order_Details_UnitPrice])
Finish editing the sheet

Re: Using OData Connector with OData v4.0 JSON Messages with Qlik Web Connectors (Qlik Cloud)

E_Langlet — Wed, 04 May 2022 09:15:59 GMT

Hi!

Is it possible to override the OData connector in the script so that you can use a variable as input to the Auth header field?

Something similar to "WITH CONNECTION" when working with the REST connector.

Re: Using OData Connector with OData v4.0 JSON Messages with Qlik Web Connectors (Qlik Cloud)

Sweta_Sharma — Thu, 12 May 2022 15:44:44 GMT

Hi @E_Langlet !

You should be able to use the following OData params:

ODataConnector_CanAuthenticate:
LOAD *
FROM [$(CONNECTION-NAME-HERE)]
(URL IS [https://qwc1.qliktech.com/data?connectorID=ODataConnector&table=CanAuthenticate&oDataServiceRoot=https%3a%2f%2fservices.odata.org%2fV4%2fNorthwind%2fNorthwind.svc%2f&acceptContentType=application%2fjson&authType=AuthHeader&authHeader=sta_gen_1%3apcLjnVBzDQvNBazWksL3iA%3d%3d%3a7Sjoiyf3Cwe1khAmwvBq3w%3d%3d&appID=&loadAccessToken=vCy6JxRQGj], qvx);

Note that the authHeader is encrypted, and you can get the encrypted value using the Helper connector (https://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/Connectors_QWC/Data-Source-Connectors/Helper-Connector.htm)-

Cheers!

Re: Using OData Connector with OData v4.0 JSON Messages with Qlik Web Connectors (Qlik Cloud)

assamf — Thu, 25 Jul 2024 13:57:32 GMT

Hello @Sweta_Sharma,

Is it still relevant ?

I'm trying to generate qvd files in a loop, and I can't figure out what script I should use to make the OData connections, I have a vTableName variable.

article Using OData Connector with OData v4.0 JSON Messages with Qlik Web Connectors (Qlik Cloud) in Official Support Articles

Using OData Connector with OData v4.0 JSON Messages with Qlik Web Connectors (Qlik Cloud)

Environment:

Real-World Scenario

Create the OData Connection

Select Tables from the OData Source

Using the JsonV4ListResources Table

Using the JsonV4GetData Table

Using the JsonV4GetDataCollection Table

Visualize the OData data

Re: Using OData Connector with OData v4.0 JSON Messages with Qlik Web Connectors (Qlik Cloud)

Re: Using OData Connector with OData v4.0 JSON Messages with Qlik Web Connectors (Qlik Cloud)

Re: Using OData Connector with OData v4.0 JSON Messages with Qlik Web Connectors (Qlik Cloud)