Using REST API - convert to table - pagination

intervigilium — Thu, 31 Aug 2023 11:52:52 GMT

Hi all,

Before i start pulling my hairs out..... first try to get some help here...

What am i trying:

Using a RESTapi connector to get data out of Topdesk. Topdesk API gives maximum of 10000 lines, so pagination is used.
So far everything is working (connecting, pagination with loop, loading information.

In de SQL statement are several tables, which i converts tot qlikview-tabels. Afterwards i drop the table from the SQL statement.

To have some overview, i removed some tables from the code, to have more clear what te problem is.

all data from API goes into RestConnectorMasterTable.
i create an TempTable with the desired data
i drop the RestConnectorMasterTable
in first loop 1 rename the TempTable to VerzamelTabel
then second loop:
i =2 --> so the else statement occurs. But Qlikview cant find the TempTable , so the concatenate gives an error.

It table doesnt exist at that moment? While in first loop he can rename the same TempTable, so in first loop no problem?

If i skip the errors, the data is in QLikview, but ofcourse the datamodel is not good (because concatenate failed).

Anyone any idea??? Thanks in advance!

Greetings Frido

SET ThousandSep='.'; SET DecimalSep=','; SET MoneyThousandSep='.'; SET MoneyDecimalSep=','; SET MoneyFormat='€ #.##0,00;€ -#.##0,00'; SET TimeFormat='hh:mm:ss'; SET DateFormat='D-M-YYYY'; SET TimestampFormat='D-M-YYYY hh:mm:ss[.fff]'; SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec'; SET DayNames='ma;di;wo;do;vr;za;zo'; SET LongMonthNames='januari;februari;maart;april;mei;juni;juli;augustus;september;oktober;november;december'; SET LongDayNames='maandag;dinsdag;woensdag;donderdag;vrijdag;zaterdag;zondag'; SET FirstWeekDay=0; SET BrokenWeeks=0; SET ReferenceDay=4; SET FirstMonthOfYear=1; SET CollationLocale='nl-NL'; //=== Initialize Let voffset = 0; Let vcount= 100; //Max results per API request //url van internet, aangepast met variabelen CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url= URL REMOVED DUE security For i = 1 To 3 RestConnectorMasterTable: SQL SELECT "id" AS "id_u14", "status" AS "status_u0", "number", "request", "requests", "action", "attachments", "branchExtraFieldA", "branchExtraFieldB", "briefDescription", "externalNumber", "callType", "object", "asset", "branch", "location", "impact", "urgency", "priority", "duration", "actualDuration", "targetDate", "onHold", "onHoldDate", "onHoldDuration", "feedbackMessage", "feedbackRating", "supplier", "processingStatus", "completed", "completedDate", "closed", "closedDate", "closureCode", "timeSpent", "timeSpentFirstLine", "timeSpentSecondLine", "timeSpentPartial", "timeSpentLinkedPartials", "timeSpentSecondLineAndPartials", "itemCosts", "objectCosts", "costs", "escalationStatus", "escalationReason", "escalationOperator", "callDate", "creator", "creationDate", "modifier", "modificationDate", "majorCall", "majorCallObject", "publishToSsd", "monitored", "expectedTimeSpent", "archivingReason", "mainIncident", "callerLocation", "subcategory", "entryType", "operatorGroup", "__KEY_root", (SELECT "id" AS "id_u2", "dynamicName", "email", "phoneNumber", "mobileNumber", "department", "__KEY_caller", "__FK_caller", (SELECT "id", "name", "clientReferenceNumber", "timeZone", "extraA", "extraB", "__FK_branch" FROM "branch" FK "__FK_branch"), (SELECT "id" AS "id_u0", "name" AS "name_u0", "__FK_department" FROM "department" FK "__FK_department"), (SELECT "id" AS "id_u1", "name" AS "name_u1", "__FK_budgetHolder" FROM "budgetHolder" FK "__FK_budgetHolder") FROM "caller" PK "__KEY_caller" FK "__FK_caller"), (SELECT "id" AS "id_u3", "name" AS "name_u2", "clientReferenceNumber" AS "clientReferenceNumber_u0", "timeZone" AS "timeZone_u0", "extraA" AS "extraA_u0", "extraB" AS "extraB_u0", "__FK_callerBranch" FROM "callerBranch" FK "__FK_callerBranch"), (SELECT "id" AS "id_u4", "name" AS "name_u3", "__FK_callerLocation" FROM "callerLocation" FK "__FK_callerLocation"), (SELECT "id" AS "id_u5", "name" AS "name_u4", "__FK_category" FROM "category" FK "__FK_category"), (SELECT "id" AS "id_u6", "name" AS "name_u5", "__FK_subcategory" FROM "subcategory" FK "__FK_subcategory"), (SELECT "id" AS "id_u7", "name" AS "name_u6", "__FK_entryType" FROM "entryType" FK "__FK_entryType"), (SELECT "id" AS "id_u8", "status", "name" AS "name_u7", "__FK_operator" FROM "operator" FK "__FK_operator"), (SELECT "id" AS "id_u9", "name" AS "name_u8", "__FK_operatorGroup" FROM "operatorGroup" FK "__FK_operatorGroup"), (SELECT "boolean1", "boolean2", "boolean3", "boolean4", "boolean5", "number1", "number2", "number3", "number4", "number5", "date1", "date2", "date3", "date4", "date5", "text1", "text2", "text3", "text4", "text5", "memo1", "memo2", "memo3", "memo4", "memo5", "searchlist1", "searchlist2", "searchlist3", "searchlist4", "searchlist5", "__FK_optionalFields1" FROM "optionalFields1" FK "__FK_optionalFields1"), (SELECT "boolean1" AS "boolean1_u0", "boolean2" AS "boolean2_u0", "boolean3" AS "boolean3_u0", "boolean4" AS "boolean4_u0", "boolean5" AS "boolean5_u0", "number1" AS "number1_u0", "number2" AS "number2_u0", "number3" AS "number3_u0", "number4" AS "number4_u0", "number5" AS "number5_u0", "date1" AS "date1_u0", "date2" AS "date2_u0", "date3" AS "date3_u0", "date4" AS "date4_u0", "date5" AS "date5_u0", "text1" AS "text1_u0", "text2" AS "text2_u0", "text3" AS "text3_u0", "text4" AS "text4_u0", "text5" AS "text5_u0", "memo1" AS "memo1_u0", "memo2" AS "memo2_u0", "memo3" AS "memo3_u0", "memo4" AS "memo4_u0", "memo5" AS "memo5_u0", "searchlist1" AS "searchlist1_u0", "searchlist2" AS "searchlist2_u0", "searchlist3" AS "searchlist3_u0", "searchlist4" AS "searchlist4_u0", "searchlist5" AS "searchlist5_u0", "__FK_optionalFields2" FROM "optionalFields2" FK "__FK_optionalFields2"), (SELECT "@Value", "__FK_externalLinks" FROM "externalLinks" FK "__FK_externalLinks" ArrayValueAlias "@Value"), (SELECT "@Value" AS "@Value_u0", "__FK_partialIncidents" FROM "partialIncidents" FK "__FK_partialIncidents" ArrayValueAlias "@Value_u0"), (SELECT "id" AS "id_u10", "name" AS "name_u9", "__FK_creator" FROM "creator" FK "__FK_creator"), (SELECT "id" AS "id_u11", "name" AS "name_u10", "__FK_modifier" FROM "modifier" FK "__FK_modifier"), (SELECT "id" AS "id_u12", "name" AS "name_u11", "__FK_processingStatus" FROM "processingStatus" FK "__FK_processingStatus"), (SELECT "id" AS "id_u13", "name" AS "name_u12", "__FK_callType" FROM "callType" FK "__FK_callType") FROM JSON (wrap on) "root" PK "__KEY_root" WITH CONNECTION(Url " URL REMOVED DUE TO SECURITY TempTable: LOAD [id_u14], [status_u0], [number], [request], [requests], [action], [attachments], [branchExtraFieldA], [branchExtraFieldB], [briefDescription], [externalNumber], [callType], [object], [asset], [branch], [location], [impact], [urgency], [priority], [duration], [actualDuration], [targetDate], [onHold], [onHoldDate], [onHoldDuration], [feedbackMessage], [feedbackRating], [supplier], [processingStatus], [completed], [completedDate], [closed], [closedDate], [closureCode], [timeSpent], [timeSpentFirstLine], [timeSpentSecondLine], [timeSpentPartial], [timeSpentLinkedPartials], [timeSpentSecondLineAndPartials], [itemCosts], [objectCosts], [costs], [escalationStatus], [escalationReason], [escalationOperator], [callDate], [creator], [creationDate], [modifier], [modificationDate], [majorCall], [majorCallObject], [publishToSsd], [monitored], [expectedTimeSpent], [archivingReason], [mainIncident], [callerLocation], [subcategory], [entryType], [operatorGroup], [__KEY_root] RESIDENT RestConnectorMasterTable WHERE NOT IsNull([__KEY_root]); Drop table RestConnectorMasterTable; If i = 1 Then //=== Create the table VerzamelTabel Rename table TempTable to VerzamelTabel; Else //=== Add new results to the table VerzamelTabel Concatenate (VerzamelTabel) LOAD * RESIDENT TempTable; Drop table TempTable; Endif; //=== Update pagination offset counter Let voffset = $(voffset) + 100; Next i; //Next Pagination

Re: Using REST API - convert to table - pagination

williejacobs — Tue, 12 Sep 2023 08:07:52 GMT

Hi @intervigilium

Instead of rename TempTable just name your table VerzamelTable as you want to name it

I do it a little differently by storing the result to a qvd and then load and concatenate each page.

topic Re: Using REST API - convert to table - pagination in Connectivity & Data Prep

Using REST API - convert to table - pagination

Re: Using REST API - convert to table - pagination