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

[Sonja_Bauernfeind]

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:

Qlik Web Connectors 
Qlik Cloud 
Qlik Sense Business 

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

1. From the Data Load Editor, create a new connection and select OData
   
   
   
   
2. In the Create new connection dialog, provide the OData Service Root (https://services.odata.org/V4/Northwind/Northwind.svc)
   
   
   
   
3. 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.

1. In the Data Load Editor, select the OData connection you created above and click the Select Data button
   
   
2. 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

1. On the Select data to load dialog select the JsonV4GetData table
   
   
2. 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.

1. On the Select data to load dialog check the JsonV4GetDataCollection table
   
   
2. Enter the value from the name column from the JsonV4ListResources table (Orders) into the OData Resource Path parameter
   
   
3. 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
   
   
4. 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
   
   
5. 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.
   
   
6. 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).

   
   
   
7. Click the Insert Script button, then Load Data from the Data Load Editor

Visualize the OData data

1. Build a new sheet in your app
   
   
2. Add a Bar Chart to the sheet
   
   
3. Add a new Dimension to your Bar Chart using the following field:
   

   JsonV4GetDataCollection.Order_Details_Product_ProductName​

   
   

4. Add a new Measure to you Bar Chart with the following expression:
   

   Sum([JsonV4GetDataCollection.Order_Details_Quantity] * [JsonV4GetDataCollection.Order_Details_UnitPrice])​

   
   
   
   

5. Finish editing the sheet
   
   

[E_Langlet]

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. 

[Sweta_Sharma]

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

Cheers!