Qlik Sense中ServiceNow统一REST连接：WITH CONNECTION无数据问题求助

Let's break down why your load is returning no data (without errors) and fix the approach step by step:

1. Critical Syntax Error: Misusing QUERY in WITH CONNECTION #

Your biggest issue is that you're putting connection-level parameters (like method, timeout, httpProtocol) inside the QUERY block. The QUERY keyword in Qlik's WITH CONNECTION is only meant for adding/overriding URL query string parameters (e.g., ServiceNow's sysparm_query, sysparm_fields). Connection configuration settings need to be declared directly as top-level entries in WITH CONNECTION.

2. Fix the Parameter Structure #

Here's a corrected version of your script, with clear explanations:

// Define your target ServiceNow table URL (use HTTPS, since you enabled allowHttpsOnly)
LET vURL = "https://your-instance.service-now.com/api/now/table/nameofthetable";

RestConnectorMasterTable:
SQL SELECT 
    // List your target fields here (match sysparm_fields below)
    sys_created_by,
    state,
    // Add other fields you need to query
FROM JSON (wrap off) "result" PK "__KEY_result"
WITH CONNECTION (
    // Connection-level settings (NOT under QUERY)
    URL "$(vURL)",
    METHOD "GET",
    TIMEOUT 900,
    HTTP_PROTOCOL "1.1",
    IS_KEEP_ALIVE true,
    BODY_ENCODING "UTF-8",
    SEND_EXPECT_100_CONTINUE true,
    AUTO_DETECT_RESPONSE_TYPE true,
    ADD_MISSING_QUERY_PARAMETERS_TO_FINAL_REQUEST false,
    ALLOW_HTTPS_ONLY true,
    USE_PROXY false,
    PROXY_BYPASS_ON_LOCAL false,
    PROXY_USE_DEFAULT_CREDENTIALS true,
    
    // Pagination configuration (also connection-level)
    PAGINATION_TYPE "Offset",
    OFFSET_START_FIELD "sysparm_offset",
    IS_OFFSET_START_FIELD_HEADER false,
    OFFSET_START_FIELD_VALUE 0,
    OFFSET_COUNT_FIELD_NAME "sysparm_limit",
    IS_OFFSET_COUNT_FIELD_HEADER false,
    OFFSET_COUNT_VALUE 10000,
    OFFSET_TOTAL_PATH "X-Total-Count",
    IS_OFFSET_TOTAL_PATH_HEADER true,
    ALLOW_RESPONSE_HEADERS false,

    // URL query parameters (each gets its own QUERY entry)
    QUERY "sysparm_query" "stateINin_progress,Closed,New",
    QUERY "sysparm_fields" "sys_created_by,state", // Match your SELECT fields
    QUERY "sysparm_display_value" "true"
);

3. Key Fixes & Best Practices #

• HTTPS vs HTTP: You set ALLOW_HTTPS_ONLY true but used an HTTP URL — this will block the request. Switch to ServiceNow's standard HTTPS endpoint (https://<instance>.service-now.com/api/now/table/<table>).
• Query Parameters: Instead of packing all query params into a single queryParameters entry, split them into individual QUERY lines. This avoids broken encoding (your original %2u/%1 looked like mangled URL syntax) and makes debugging easier.
• Field Alignment: Ensure the fields in sysparm_fields exactly match the fields you're selecting in the SQL SELECT clause. ServiceNow will only return the fields specified in sysparm_fields.
• Test the URL First: Before running the Qlik script, paste the full generated URL (with query params) into your browser (while authenticated to ServiceNow) to confirm it returns valid JSON data. This verifies the API endpoint and parameters work outside Qlik.

4. Additional Debugging Steps #

If you still get no data after fixing the script:

• Check Qlik's Script Execution Log for subtle warnings (even if no error is thrown, there might be hints about authentication or parameter issues).
• Verify your REST connector's base authentication (username/password/OAuth) is correctly configured for your ServiceNow instance.
• Test with a smaller OFFSET_COUNT_VALUE (e.g., 100) to rule out pagination-related issues.

内容的提问来源于stack exchange，提问作者ilikawa