Fix failed connection to Qlik with status code 401

Question

I am trying to generate detailed python scripts to read Json data from a Qlik cloud bar chart, output it as a pdf file on my laptop but I keep getting an error message 401. i,e Failed to fetch data:

Failed to fetch data. Status code: 401 # output from the try loop in the code below

# Then full error traceback:
Traceback (most recent call last):
  File "c:\Users\Sunday\Documents\ClikModules\appv3ntpj.py", line 41, in <module>
    plt.bar(df['Customer'], df['Sum(Sales)'])
            ~~^^^^^^^^^^^^
  File "C:\Users\Sunday\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py", line 3893, in __getitem__
    indexer = self.columns.get_loc(key)
              ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Sunday\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\range.py", line 418, in get_loc
    raise KeyError(key)
KeyError: 'Customer'

The code below cannot connect, thus the try loop isses an empty dataframe, after which the attempt to plot it, understandably, raises the error described in the above traceback.

import requests
import pandas as pd
import matplotlib.pyplot as plt
import base64

# Qlik Sense Configuration
qlik_base_url = "https://ioco2.eu.qlikcloud.com/"
qlik_app_id = "d0c200c8-b3bb-4157-81b7-4d18d44856a3"
qlik_table_object_id = "tNfNKkC"

# Authentication credentials (replace with your actual credentials)
qlik_username = "my username"
qlik_password = "password"

# Fetch data from Qlik Sense with authentication headers
qlik_data_url = f"{qlik_base_url}/api/v1/apps/{qlik_app_id}/tables/{qlik_table_object_id}/data"
headers = {
    "Authorization": "Basic " + base64.b64encode(f"{qlik_username}:{qlik_password}".encode()).decode()
}
response = requests.get(qlik_data_url, headers=headers)

# Check for successful response status
if response.status_code == 200:
    try:
        table_data = response.json()
    except requests.exceptions.JSONDecodeError as e:
        print(f"Error decoding JSON: {e}")
        table_data = None
else:
    print(f"Failed to fetch data. Status code: {response.status_code}")
    table_data = None

# Continue with the rest of the script...

# Convert Qlik table data to a Pandas DataFrame
df = pd.DataFrame(table_data) # So this will be empty

# Data processing or analysis (replace this with your specific requirements)
# For this example, let's just plot the data and save it as a PDF
plt.figure(figsize=(10, 6))
plt.bar(df['Customer'], df['Sum(Sales)']) # raises the error
plt.xlabel('Customer')
plt.ylabel('Sum(Sales)')
plt.title('Top Customers by Sales in Qlik Sense')

Please help me fix this status code 401.

Answer 1

I have gotten the answer, here is the solution:

import pytoqlik
import seaborn  # Seaborn provides us with some sample datasets 
import pandas as pd   # We will need pandas to manipulate the extracted DataFrame

Generate the api-key from the Qlik console and do the following:

key='eyJhbGciOiJFUzM4NCIsImtpZCI6IjczZWUwNDgzLTc1NGYtNDc3Yy'
url='https://pytoqlik-bestlib.us.qlikcloud.com/'
ID='5078a285-39f8-4bd1-8b1b-351d6cef77ea'

p2q = pytoqlik.Pytoqlik(api_key=key, tenant=url, appId=appId)

with the above you can read any object from the Qlik like the one below:

p2q.toPy('qCZbkW')

Answer 2

Status code 401 does not appear in Qlik list of response codes. This might rather be a Qlik known bug, according to this Qlik Community post from 2022: Qlik Data Gateway connection reload fails with error 401: "R&D has identified this issue as a defect and is working on a fix"

Contact their support, and hopefully by now they can help.

Answer 3

I don't recognize the /api/v1/apps/{qlik_app_id}/tables/{qlik_table_object_id}/data endpoint you mentioned and don't see it in the specs for Qlik Cloud's REST API. Either way, I don't think you can use the REST API to get data out of Qlik Sense app objects. You will instead need to utilize the JSON RPC API, as that is the service that lets you open an app and interact with the objects therein. That service is accessed over WebSockets.

Seeing as you're using Python for your project, one option here would be to utilize the official Qlik Platform SDK library, installed easily from PyPi:

python3 -m pip install --upgrade qlik-sdk

This library is great, as it is a wrapper around both the REST and JSON APIs, meaning you don't have to write as much code or deal with WebSocket stuff.

Assuming you go down the Platform SDK library route, your code would need to achieve a few things:

• Authenticate to your Qlik Cloud tenant
• Open an app
• Get the layout for the table object
• Get the hypercube data for the table object
• Collect and order the dimensions and measures used in the object to match the hypercube column sort order
• Format and store the rows of data from the data pages in the hypercube
• Open as a Pandas dataframe

Here's an example script you can use, it's been tested as of this writing and confirmed to run successfully:

from qlik_sdk import Apps, AuthType, Config, NxApp
from qlik_sdk.apis.Qix import NxPage, HyperCube
import pandas as pd

# connect to Qlik engine

base_url = "https://your-tenant.eu.qlikcloud.com/"
api_key = "xxxxxx"
apps = Apps(Config(host=base_url, auth_type=AuthType.APIKey, api_key=api_key))

# app is fetched from the REST /v1/apps/{app_id}

app = apps.get("xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx")


def get_ordered_cols_qlik_hc(hc: HyperCube) -> list:
    """Get ordered columns from Qlik hypercube object."""

    # get object columns

    dim_names = [d.qFallbackTitle for d in hc.qDimensionInfo]
    meas_names = [m.qFallbackTitle for m in hc.qMeasureInfo]
    obj_cols = dim_names.copy()
    obj_cols.extend(meas_names)

    # order column array to match hypercube column order

    new_cols = []
    new_col_order = hc.qColumnOrder
    for c in new_col_order:
        new_cols.append(obj_cols[c])

    return new_cols


def get_qlik_obj_data(app: NxApp, obj_id: str) -> list:
    """Get data from an object in a Qlik app."""

    # opens a websocket connection against the Engine API and gets the app hypercube

    with app.open():
        tbl_obj = app.get_object(obj_id)
        tbl_layout = tbl_obj.get_layout()
        tbl_size = tbl_layout.qHyperCube.qSize
        tbl_hc = tbl_obj.get_hyper_cube_data(
            "/qHyperCubeDef",
            [NxPage(qHeight=tbl_size.qcy, qWidth=tbl_size.qcx, qLeft=0, qTop=0)],
        )

    hc_cols = get_ordered_cols_qlik_hc(tbl_layout.qHyperCube)

    # traverse data pages and store dict for each row

    hc_cols_count = len(hc_cols)
    tbl_data = []

    for data_page in tbl_hc:
        for rows in data_page.qMatrix:
            row = {hc_cols[i]: rows[i].qText for i in range(hc_cols_count)}
            tbl_data.append(row)

    return tbl_data


# store as a Pandas dataframe

obj_data = get_qlik_obj_data(app=app, obj_id="xxxxxx")

df = pd.DataFrame(obj_data)

The code is organized into two functions: one that takes a hypercube and returns a list of columns in the correct order; and then the main one that takes an app and an object ID and returns a list of dictionaries, which is essentially a list of each row of data:

[
    {'columnA': 'aaa', 'columnB': 'bbb', 'columnC': 'ccc'},
    {'columnA': 'fff', 'columnB': 'ggg', 'columnC': 'hhh'},
    {'columnA': 'xxx', 'columnB': 'yyy', 'columnC': 'zzz'}
]