Qlik Community

Knowledge

Search or browse our knowledge base to find answers to your questions ranging from account questions to troubleshooting error messages. The content is curated and updated by our global Support team

Announcements
June 28, 10AM ET: Qlik Nation and Qlik Community present: CyberSleuth REGISTER TODAY

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

cancel
Showing results for 
Search instead for 
Did you mean: 
Sonja_Bauernfeind
Digital Support
Digital Support

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

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

    Select OData Data Load Editor.png

  2. In the Create new connection dialog, provide the OData Service Root (https://services.odata.org/V4/Northwind/Northwind.svc)

    create new connection odata.png

  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

    OData Select Orders.png

    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

    Preview Data.png

    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

      OData OrderId.png

    • the OData collection column name – in this case, Order_Details

      Odata Order Details.png

 

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

    Preview Data again.png

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

    Select data to load odata.png

  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​


    Add New Barchart.png

  4. Add a new Measure to you Bar Chart with the following expression:
    Sum([JsonV4GetDataCollection.Order_Details_Quantity] * [JsonV4GetDataCollection.Order_Details_UnitPrice])​


    Add new measure.png

    Add new measure screenshot 2.png

  5. Finish editing the sheet

    my new sheet.png
Labels (2)
Comments
E_Langlet
Partner
Partner

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
Support
Support

Hi @E_Langlet !

You should be able to use the following OData params:

Sweta_Sharma_1-1652370084341.png                Sweta_Sharma_2-1652370125566.png

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

Sweta_Sharma_0-1652370027019.png

Cheers!

Version history
Last update:
‎2022-05-10 03:14 PM
Updated by: