Using OData Connector with OData v4.0 JSON Messages with Standalone Qlik Web Connectors

[Sonja_Bauernfeind]

Qlik Web Connectors support OData v4.0 and responses in JSON format. The support was introduced in the standalone connectors at the March 2022 release (2.122.1).

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

Environment:

Qlik Web Connectors March 2022 (2.122.1) and later
Qlik Sense Enterprise on Windows 

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

Obtain Qlik Sense Script from Qlik Web Connectors Standalone

Using the JsonV4ListResources Table

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

1. In Qlik Web Connectors Standalone, choose the Qlik OData Connector
   
   
2. Select the JsonV4ListResources table and click the Parameters button
   
   
3. Provide the OData Service Root (https://services.odata.org/V4/Northwind/Northwind.svc) and click the Save Inputs and Run Table 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. Go back to the OData table list from the JsonV4ListResources table by selecting
   

   “< JsonV4ListResources“​

2. Select the JsonV4GetData table and click the Parameters button
   
   
3. Enter the value from the name column from the JsonV4ListResources table (Orders) into the OData Resource Path parameter and click the Save Inputs and Run Table 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. Go back to the OData table list from the JsonV4GetData table by selecting
   

   “< JsonV4GetData“​

2. Select the JsonV4GetDataCollection table and click the Parameters button
   
   
3. Enter the value from the name column from the JsonV4ListResources table (Orders) into the OData Resource Path parameter
   
   
4. 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
   
   
5. 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
   
   
6. 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.
   
   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 Save Inputs & Run Table button
   
   
   
   
8. Click Qlik Sense (Standard Mode) tab and click to Copy Script to Clipboard button
   
   

Add Your Script to Qlik Sense

Paste the Qlik Web Connector OData script you copied above into your Qlik Sense Server app.

1. Open a Qlik Sense Server App
   
   
2. Navigate to the Data Load Editor
   
   
3. Create a new Web File connection and use the URL to your Qlik Web Connector Standalone instance
   
   
   
   
   
   
4. Add the variable assignment for the new Web File connection to the Data Load Editor
   

   let vQwcConnectionName = ‘lib//QwcStandalone (your user)’;​

5. Paste the script from Qlik Web Connector Standalone into the Data Load Editor
   
   
6. Click the Load Data button

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 and Apply:
   

   Sum([JsonV4GetDataCollection_Order_Details_Quantity] * [JsonV4GetDataCollection_Order_Details_UnitPrice])​

   
   

   
   
   
5. Finish editing the sheet