Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save $300 - Register for QlikWorld (formerly Qonnections) by January 31st: Learn More
Juancadlc95
New 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
andoryuu
Contributor III

Re: Make a loop to request SaleId

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.