Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Make a loop to request SaleId

Im trying to request SaleId for this url

https://server."company".com/masterkey/agency/sale/<saleid>/saleOrder

So that I can get country and city of destination of the SaleOrder.

I want to make a loop so that every call I make, it will return the information of the saleOrder im calling.

If there is someone that could help me that would be great!!!!!

I have this already in the script

LIB CONNECT TO 'Sale';

RestConnectorMasterTable:
SQL SELECT
"__KEY_root",
(SELECT
"sort",
"order",
"max",
"offset",
"totalCount",
"count",
"__FK_meta"
FROM "meta" FK "__FK_meta"),
(SELECT
"q",
"courseType",
"status",
"interestLevel",
"referrer",
"paymentStatus",
"enrollmentStatus",
"seller",
"client",
"saleGroup",
"isEnabled",
"studentName",
"saleOrderStartDate",
"courseName",
"customStatus",
"channel",
"manager",
"__KEY_query",
"__FK_query",
(SELECT
"@Value",
"__FK_distributor"
FROM "distributor" FK "__FK_distributor" ArrayValueAlias "@Value")
FROM "query" PK "__KEY_query" FK "__FK_query"),
(SELECT
"id" AS "id_u5",
"version",
"pid",
"externalId",
"name" AS "name_u5",
"status" AS "status_u0",
"courseType" AS "courseType_u0",
"dateCreated" AS "dateCreated_u0",
"lastUpdated",
"referrer" AS "referrer_u0",
"interestLevel" AS "interestLevel_u0",
"campaign",
"formURL",
"customStatus" AS "customStatus_u0",
"saleGroup" AS "saleGroup_u0",
"isEnabled" AS "isEnabled_u0",
"manager" AS "manager_u0",
"discardReason",
"enrollmentFieldApproved",
"enrollmentFieldCount",
"paymentDueDate",
"paymentStatus" AS "paymentStatus_u0",
"enrollmentStatus" AS "enrollmentStatus_u0",
"__KEY_resourceList",
"__FK_resourceList",
(SELECT
"id",
"name",
"email",
"firstName",
"lastName",
"birthday",
"gender",
"__KEY_client",
"__FK_client",
(SELECT
"number",
"type",
"__FK_phone"
FROM "phone" FK "__FK_phone")
FROM "client" PK "__KEY_client" FK "__FK_client"),
(SELECT
"name" AS "name_u0",
"nationality",
"birthday" AS "birthday_u0",
"occupation",
"firstName" AS "firstName_u0",
"lastName" AS "lastName_u0",
"email" AS "email_u0",
"__FK_student"
FROM "student" FK "__FK_student"),
(SELECT
"id" AS "id_u0",
"name" AS "name_u1",
"email" AS "email_u1",
"iconUri",
"__FK_seller"
FROM "seller" FK "__FK_seller"),
(SELECT
"id" AS "id_u1",
"name" AS "name_u2",
"__FK_distributor_u0"
FROM "distributor" FK "__FK_distributor_u0"),
(SELECT
"departureDate",
"destination",
"interest",
"budget",
"channel" AS "channel_u0",
"comments",
"dateCreated",
"reporter",
"__FK_lead"
FROM "lead" FK "__FK_lead"),
(SELECT
"id" AS "id_u2",
"name" AS "name_u3",
"__FK_customStatus"
FROM "customStatus" FK "__FK_customStatus"),
(SELECT
"id" AS "id_u3",
"start",
"__FK_saleOrder"
FROM "saleOrder" FK "__FK_saleOrder"),
(SELECT
"id" AS "id_u4",
"name" AS "name_u4",
"__FK_saleGroup"
FROM "saleGroup" FK "__FK_saleGroup")
FROM "resourceList" PK "__KEY_resourceList" FK "__FK_resourceList")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (Url "$(vURL)");


[client.sale]:
LOAD [id] as id.sale,
[name] as name.sale,
[email] as email.sale,
[firstName] as [firstName.sale],
[lastName] as [lastName.sale],
[birthday] as [birthday.sale],
[gender] as [gender.sale],
[__KEY_client] as [__KEY_client.sale],
[__FK_client] AS [__KEY_resourceList.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_client]);

store [client.sale] into [lib://QVD/client.sale.qvd] (qvd);
drop table [client.sale];


[student.sale]:
LOAD [name_u0] as [name_u0.sale],
[nationality] as [nationality.sale],
[birthday_u0] as [birthday_u0.sale],
[occupation] as [occupation.sale],
[firstName_u0] as [firstName_u0.sale],
[lastName_u0] as [lastName_u0.sale],
[email_u0] as [email_u0.sale],
[__FK_student] AS [__KEY_resourceList.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_student]);

store [student.sale] into [lib://QVD/student.sale.qvd] (qvd);
drop table [student.sale];


[seller.sale]:
LOAD [id_u0] as [id_u0.sale],
[name_u1] as [name_u1.sale],
[email_u1] as [email_u1.sale],
[iconUri] as [iconUri.sale],
[__FK_seller] AS [__KEY_resourceList.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_seller]);

store [seller.sale] into [lib://QVD/seller.sale.qvd] (qvd);
drop table [seller.sale];


[distributor_u0.sale]:
LOAD [id_u1] as [id_u1.sale],
[name_u2] as [name_u2.sale],
[__FK_distributor_u0] AS [__KEY_resourceList.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_distributor_u0]);

store [distributor_u0.sale] into [lib://QVD/distributor_u0.sale.qvd] (qvd);
drop table [distributor_u0.sale];


[lead.sale]:
LOAD [departureDate] as [departureDate.sale],
[destination] as [destination.sale],
[interest] as [interest.sale],
[budget] as [budget.sale],
[channel_u0] as [channel_u0.sale],
[comments] as [comments.sale],
[dateCreated] as [dateCreated.sale],
[reporter] as [reporter.sale],
[__FK_lead] AS [__KEY_resourceList.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_lead]);

store [lead.sale] into [lib://QVD/lead.sale.qvd] (qvd);
drop table [lead.sale];


[customStatus.sale]:
LOAD [id_u2] as [id_u2.sale],
[name_u3] as [name_u3.sale],
[__FK_customStatus] AS [__KEY_resourceList.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customStatus]);

store [customStatus.sale] into [lib://QVD/customStatus.sale.qvd] (qvd);
drop table [customStatus.sale];


[saleOrder.sale]:
LOAD [id_u3] as [id_u3.sale],
[start] as [start.sale],
[__FK_saleOrder] AS [__KEY_resourceList.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_saleOrder]);

store [saleOrder.sale] into [lib://QVD/saleOrder.sale.qvd] (qvd);
drop table [saleOrder.sale];


[saleGroup.sale]:
LOAD [id_u5] as [id_u5.sale],
[name_u5] as [name_u5.sale],
[__FK_saleGroup] AS [__KEY_resourceList.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_saleGroup]);

store [saleGroup.sale] into [lib://QVD/saleGroup.sale.qvd] (qvd);
drop table [saleGroup.sale];


[resourceList.sale]:
LOAD [id_u4] as [id_u4.sale],
[version] as [version.sale],
[pid] as [pid.sale],
[externalId] as [externalId.sale],
[name_u4] as [name_u4.sale],
[status_u0] as [status_u0.sale],
[courseType_u0] as [courseType_u0.sale],
[dateCreated_u0] as [dateCreated_u0.sale],
[lastUpdated] as [lastUpdated.sale],
[referrer_u0] as [referrer_u0.sale],
[interestLevel_u0] as [interestLevel_u0.sale],
[campaign] as [campaign.sale],
[formURL] as [formURL.sale],
[customStatus_u0] as [customStatus_u0.sale],
[saleGroup_u0] as [saleGroup_u0.sale],
[isEnabled_u0] as [isEnabled_u0.sale],
[manager_u0] as [manager_u0.sale],
[discardReason] as [discardReason.sale],
[enrollmentFieldApproved] as [enrollmentFieldApproved.sale],
[enrollmentFieldCount] as [enrollmentFieldCount.sale],
[paymentDueDate] as [paymentDueDate.sale],
[paymentStatus_u0] as [paymentStatus_u0.sale],
[enrollmentStatus_u0] as [enrollmentStatus_u0.sale],
[__KEY_resourceList] as [__KEY_resourceList.sale],
[__FK_resourceList] AS [__KEY_root.sale]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_resourceList]);

store [resourceList.sale] into [lib://QVD/resourceList.sale.qvd] (qvd);
drop table [resourceList.sale];


DROP TABLE RestConnectorMasterTable;

1 Reply
Creator III
Creator III

We do this all the time with REST connectors.  If you have a table that has your SaleIds in it called "tableHoldingYourSaleIDs" this is how this would work.  Note, you should accumulate all your data into single tables and write the QVDs out once unless you are going to name them differently - otherwise you're only going to get the last saleid.

 

LIB CONNECT TO 'Sale';

for i=1 to NoOfRows('tableHoldingYourSaleIDs')

let vSaleId=FieldValue('SaleId',$(i));

let vURL='https://server."company".com/masterkey/agency/sale/$(vSaleId)/saleOrder'

#do your stuff here
WITH CONNECTION (Url "$(vURL)");

#accumulate it into tables.

next

store [seller.sale] into [lib://QVD/seller.sale.qvd] (qvd); #unless you write out the QVDs with the saleID in it - which you can do with the variable.
drop table [seller.sale]; 

#more QVDs here.