Before i start pulling my hairs out..... first try to get some help here...
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.
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).
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
