Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.