qlikview - 使用循环的 Qliksense REST 偏移分页

问题描述

我需要对来自 Hubspot API 的所有记录进行分页，但我陷入了偏移分页循环。根据 Hubspot 的 API 文档，响应中没有可用的“总记录”路径，而是“有更多”字段告诉我们是否有更多记录可以从该门户中提取。可用于分页的两个参数是

vidOffset & has-more

这是通过 rest 连接器进行自定义分页的 qliksense 脚本的样子。

LIB CONNECT TO 'HubSpot ';
// Action required: Implement the logic to retrieve the total records from the REST source and assign to the 'total' local variable.
Let total = 0;

Let totalfetched = 0;
Let startAt = 0;
Let pageSize = 100;

for startAt = 0 to total step pageSize
RestConnectorMasterTable:
SQL SELECT 
    "has-more",
    "vid-offset",
    "__KEY_root",
    (SELECT 
        "addedAt",
        "vid" AS "vid_u0",
        "canonical-vid",
        "portal-id",
        "is-contact",
        "profile-token",
        "profile-url",
        "__KEY_contacts",
        "__FK_contacts",
        (SELECT 
            "@Value",
            "__FK_merged-vids"
        FROM "merged-vids" FK "__FK_merged-vids" ArrayValueAlias "@Value"),
        (SELECT 
            "__KEY_properties",
            "__FK_properties",
            (SELECT 
                "value",
                "__FK_firstname"
            FROM "firstname" FK "__FK_firstname"),
            (SELECT 
                "value" AS "value_u0",
                "__FK_lastmodifieddate"
            FROM "lastmodifieddate" FK "__FK_lastmodifieddate"),
            (SELECT 
                "value" AS "value_u1",
                "__FK_company"
            FROM "company" FK "__FK_company"),
            (SELECT 
                "value" AS "value_u2",
                "__FK_lastname"
            FROM "lastname" FK "__FK_lastname")
        FROM "properties" PK "__KEY_properties" FK "__FK_properties"),
        (SELECT 
            "@Value" AS "@Value_u0",
            "__FK_form-submissions"
        FROM "form-submissions" FK "__FK_form-submissions" ArrayValueAlias "@Value_u0"),
        (SELECT 
            "vid",
            "saved-at-timestamp",
            "deleted-changed-timestamp",
            "__KEY_identity-profiles",
            "__FK_identity-profiles",
            (SELECT 
                "type",
                "value" AS "value_u3",
                "timestamp",
                "is-primary",
                "__FK_identities"
            FROM "identities" FK "__FK_identities")
        FROM "identity-profiles" PK "__KEY_identity-profiles" FK "__FK_identity-profiles"),
        (SELECT 
            "@Value" AS "@Value_u1",
            "__FK_merge-audits"
        FROM "merge-audits" FK "__FK_merge-audits" ArrayValueAlias "@Value_u1")
    FROM "contacts" PK "__KEY_contacts" FK "__FK_contacts")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(Url "https://api.hubapi.com/contacts/v1/lists/all/contacts/all");
// Action required: change URL included in 'WITH CONNECTION' as needed to support pagination for the REST source. 
// Please see the documentation for "Loading paged data."

NEXT startAt;

需要了解如何根据我的 API 参数进行设置，即 offset 和 hasmore 属性。我如何循环遍历所有的 vidoffset 值，以便我可以获得所有记录，直到 has-more 变为 false？

这是我的json回复

标签： qlikviewqliksense

请尝试递归调用，您是否需要将调用放入子例程中，而不是检查 has_more 以及它是否等于 True 再次调用子例程。此外，每次都必须使用新的 vid-offset 值更新 Url 参数。这是示例（经过测试可以正常工作）：

SUB getOnePage(vOffset)

  LIB CONNECT TO [hubspot_api];

  RestConnectorMasterTable:
  SQL SELECT 
  (...)
  FROM JSON (wrap on) "root" PK "__KEY_root"
  WITH CONNECTION (Url "https://api.hubapi.com/contacts/v1/lists/all/contacts/all/?hapikey=YOURKEY=$(vOffset)");

  LET vHasMore = Peek('has-more',-1);
  LET vVidOffset = Peek('vid-offset',-1);

  DROP Table root;

  IF vHasMore = 'True' then

    CALL getOnePage($(vVidOffset));

  EndIf

EndSub

由于每个 CALL 中的重复键，我们需要更改 REST 连接器中的设置，如下所示：

qlikview - 使用循环的 Qliksense REST 偏移分页

问题描述

解决方案

推荐阅读