Solved: Re: REST API Can't find 'Total field path ' Error - Qlik Community

DrB1 · ‎2022-01-12

I need rescued - I have tried so many things and cannot seem to get all my data (4775 records). With some settings I get the 429 error so I am trying to add headers to adjust to the api provider's requirements. Now it appears to want to run, but can't find the total records field. Here's the script (basically getting every field offered) and the error message I'm getting now. I'm trying to use the offset pagination required by the provider. Can someone help me, please??

RestConnectorMasterTable:
SQL SELECT
"Date",
"Transfer-Encoding",
"Connection",
"Server",
"X-Frame-Options",
"X-XSS-Protection",
"X-Content-Type-Options",
"X-Download-Options",
"X-Permitted-Cross-Domain-Policies",
"Referrer-Policy",
"X-PCO-API-Auth-Method",
"X-PCO-API-Request-Rate-Count",
"X-PCO-API-Request-Rate-Limit",
"X-PCO-API-Request-Rate-Period",
"X-PCO-API-Processor",
"Access-Control-Allow-Credentials",
"Access-Control-Expose-Headers",
"Access-Control-Allow-Headers",
"Access-Control-Allow-Methods",
"Access-Control-Allow-Origin",
"Vary",
"X-PCO-API-Processed-As-Version",
"ETag",
"Cache-Control",
"X-Request-ID",
"X-Runtime",
"Strict-Transport-Security",
"Content-Type",
"__KEY__response_header",
(SELECT
"__KEY_root",
"__FK_root",
(SELECT
"self",
"next",
"__FK_links"
FROM "links" FK "__FK_links"),
(SELECT
"type" AS "type_u0",
"id" AS "id_u0",
"__KEY_data",
"__FK_data",
(SELECT
"accounting_administrator",
"anniversary",
"avatar",
"birthdate",
"can_create_forms",
"child",
"created_at",
"demographic_avatar_url",
"directory_status",
"first_name",
"gender",
"given_name",
"grade",
"graduation_year",
"inactivated_at",
"last_name",
"medical_notes",
"membership",
"middle_name",
"name",
"nickname",
"passed_background_check",
"people_permissions",
"remote_id",
"school_type",
"site_administrator",
"status",
"updated_at",
"__FK_attributes"
FROM "attributes" FK "__FK_attributes"),
(SELECT
"__KEY_relationships",
"__FK_relationships",
(SELECT
"data",
"__KEY_primary_campus",
"__FK_primary_campus",
(SELECT
"type",
"id",
"__FK_data_u0"
FROM "data" FK "__FK_data_u0")
FROM "primary_campus" PK "__KEY_primary_campus" FK "__FK_primary_campus")
FROM "relationships" PK "__KEY_relationships" FK "__FK_relationships"),
(SELECT
"self" AS "self_u0",
"__FK_links_u0"
FROM "links" FK "__FK_links_u0")
FROM "data" PK "__KEY_data" FK "__FK_data"),
(SELECT
"@Value",
"__FK_included"
FROM "included" FK "__FK_included" ArrayValueAlias "@Value"),
(SELECT
"total_count",
"count",
"__KEY_meta",
"__FK_meta",
(SELECT
"offset",
"__FK_next"
FROM "next" FK "__FK_next"),
(SELECT
"@Value" AS "@Value_u0",
"__FK_can_order_by"
FROM "can_order_by" FK "__FK_can_order_by" ArrayValueAlias "@Value_u0"),
(SELECT
"@Value" AS "@Value_u1",
"__FK_can_query_by"
FROM "can_query_by" FK "__FK_can_query_by" ArrayValueAlias "@Value_u1"),
(SELECT
"@Value" AS "@Value_u2",
"__FK_can_include"
FROM "can_include" FK "__FK_can_include" ArrayValueAlias "@Value_u2"),
(SELECT
"@Value" AS "@Value_u3",
"__FK_can_filter"
FROM "can_filter" FK "__FK_can_filter" ArrayValueAlias "@Value_u3"),
(SELECT
"id" AS "id_u1",
"type" AS "type_u1",
"__FK_parent"
FROM "parent" FK "__FK_parent")
FROM "meta" PK "__KEY_meta" FK "__FK_meta")
FROM (wrap on) "root" PK "__KEY_root" FK "__FK_root")
FROM JSON "_response_header" PK "__KEY__response_header";

[links]:
LOAD [self],
[next],
[__FK_links] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_links]);

[attributes]:
LOAD [accounting_administrator],
[anniversary],
[avatar],
[birthdate],
[can_create_forms],
[child],
[created_at],
[demographic_avatar_url],
[directory_status],
[first_name],
[gender],
[given_name],
[grade],
[graduation_year],
[inactivated_at],
[last_name],
[medical_notes],
[membership],
[middle_name],
[name],
[nickname],
[passed_background_check],
[people_permissions],
[remote_id],
[school_type],
[site_administrator],
[status],
[updated_at],
[__FK_attributes] AS [__KEY_data]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_attributes]);

[data]:
LOAD [type],
[id],
[__FK_data_u0] AS [__KEY_primary_campus]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_data_u0]);

[primary_campus]:
LOAD [data],
[__KEY_primary_campus],
[__FK_primary_campus] AS [__KEY_relationships]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_primary_campus]);

[relationships]:
LOAD [__KEY_relationships],
[__FK_relationships] AS [__KEY_data]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_relationships]);

[links_u0]:
LOAD [self_u0] AS [self_u0],
[__FK_links_u0] AS [__KEY_data]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_links_u0]);

[data_u0]:
LOAD [type_u0] AS [type_u0],
[id_u0] AS [id_u0],
[__KEY_data],
[__FK_data] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_data]);

[included]:
LOAD [@Value],
[__FK_included] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_included]);

[next]:
LOAD [offset],
[__FK_next] AS [__KEY_meta]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_next]);

[can_order_by]:
LOAD [@Value_u0] AS [@Value_u0],
[__FK_can_order_by] AS [__KEY_meta]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_can_order_by]);

[can_query_by]:
LOAD [@Value_u1] AS [@Value_u1],
[__FK_can_query_by] AS [__KEY_meta]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_can_query_by]);

[can_include]:
LOAD [@Value_u2] AS [@Value_u2],
[__FK_can_include] AS [__KEY_meta]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_can_include]);

[can_filter]:
LOAD [@Value_u3] AS [@Value_u3],
[__FK_can_filter] AS [__KEY_meta]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_can_filter]);

[parent]:
LOAD [id_u1] AS [id_u1],
[type_u1] AS [type_u1],
[__FK_parent] AS [__KEY_meta]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_parent]);

[meta]:
LOAD [total_count],
[count],
[__KEY_meta],
[__FK_meta] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_meta]);

[root]:
LOAD [__KEY_root],
[__FK_root] AS [__KEY__response_header]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_root]);

[_response_header]:
LOAD [Date],
[Transfer-Encoding],
[Connection],
[Server],
[X-Frame-Options],
[X-XSS-Protection],
[X-Content-Type-Options],
[X-Download-Options],
[X-Permitted-Cross-Domain-Policies],
[Referrer-Policy],
[X-PCO-API-Auth-Method],
[X-PCO-API-Request-Rate-Count],
[X-PCO-API-Request-Rate-Limit],
[X-PCO-API-Request-Rate-Period],
[X-PCO-API-Processor],
[Access-Control-Allow-Credentials],
[Access-Control-Expose-Headers],
[Access-Control-Allow-Headers],
[Access-Control-Allow-Methods],
[Access-Control-Allow-Origin],
[Vary],
[X-PCO-API-Processed-As-Version],
[ETag],
[Cache-Control],
[X-Request-ID],
[X-Runtime],
[Strict-Transport-Security],
[Content-Type],
[__KEY__response_header]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY__response_header]);

DROP TABLE RestConnectorMasterTable;

Levi_Turner · ‎2022-01-13

Does root/links/next + Next URL in pagination work for you?

View solution in original post

Levi_Turner · ‎2022-01-13

I haven't seen this myself but my best guess is the pagination settings are off. What do you have in the pagination settings in the connector config and can you provide an example response from the API for how it provides the next URL? For example in Qlik SaaS it would be like this:

  "links": {
    "self": {
      "href": "https://<myTenant>.us.qlikcloud.com/api/v1/items"
    },
    "next": {
      "href": "https://<myTenant>.us.qlikcloud.com/api/v1/items?next=FgAAAAdfaWQAXqrcirVtPQABnKexAA"
    }
  }

DrB1 · ‎2022-01-13

@Levi_Turner - thanks! Here's what the provider api explorer shows and my pagination settings screenshots:

"root":{

4 items

"links":{

2 items

"self":

"https://api.provider.com/people/v2/people"

"next":

"https://api.provider.com/people/v2/people?offset=25"

}

"data":[

25 items

0:{

...

}

5 items

1:{

...

}

5 items

2:{

...

}

5 items

3:{

...

}

5 items

4:{

...

}

5 items

5:{

...

}

5 items

6:{

...

}

5 items

7:{

...

}

5 items

8:{

...

}

5 items

9:{

...

}

5 items

10:{

...

}

5 items

11:{

...

}

5 items

12:{

...

}

5 items

13:{

...

}

5 items

14:{

...

}

5 items

15:{

...

}

5 items

16:{

...

}

5 items

17:{

...

}

5 items

18:{

...

}

5 items

19:{

...

}

5 items

20:{

...

}

5 items

21:{

...

}

5 items

22:{

...

}

5 items

23:{

...

}

5 items

24:{

...

}

5 items

]

"included":[]

0 items

"meta":{

8 items

"total_count":

4775

"count":

25

"next":{

...

}

1 item

"can_order_by":[

...

]

20 items

"can_query_by":[

...

]

27 items

"can_include":[

...

]

15 items

"can_filter":[

...

]

3 items

"parent":{

...

}

2 items

}

Levi_Turner · ‎2022-01-13

Does root/links/next + Next URL in pagination work for you?

DrB1 · ‎2022-01-13

@Levi_Turner it absolutely does!!! Thank you so much!!

DrB1 · ‎2022-01-18

@Levi_Turner - turns out that my responses are sporadic because I hit a 429 error. I believe it is due to needed headers for rate limiting. I've tried adding 3 of them in the Query Headers section of the Rest connector, matching what I see in the Response Headers. Am I missing something? Is this how you slow down the calls so you don't surpass the limits?