Exit Do Loop

tmumaw — Mon, 22 Apr 2019 18:59:49 GMT

Hi all,

Need another set of eyes....can anyone see why my Do while would not work? It should do it while length of vURL is > 0.

Thanks

LET vURL = 'https://api8preview.sapsf.com:443/odata/v2/Candidate';

//CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=$(vURL);timeout=900;method=GET;sendExpect100Continue=True;autoDetectResponseType=true;checkResponseTypeOnTestConnection=true;keyGenerationStrategy=0;authSchema=basic;skipServerCertificateValidation=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;queryParameters=$format%2json;PaginationType=None;allowResponseHeaders=false;allowHttpsOnly=false;XUserId=XKQJXJZIBCRaUEZJSBQIWQZOFLeIHZVMFDVCDHB;XPassword=PCRGRSUEQLYGXZdOPTcIWSIGXLZYWRdNNLZCDZMGUJNKSYMf;";

DO while LEN(vURL) > 0
CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=$(vURL);timeout=900;method=GET;sendExpect100Continue=True;autoDetectResponseType=true;checkResponseTypeOnTestConnection=true;keyGenerationStrategy=0;authSchema=basic;skipServerCertificateValidation=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;queryParameters=$format%2json;PaginationType=None;allowResponseHeaders=false;allowHttpsOnly=false;XUserId=XKQJXJZIBCRaUEZJSBQIWQZOFLeIHZVMFDVCDHB;XPassword=PCRGRSUEQLYGXZdOPTcIWSIGXLZYWRdNNLZCDZMGUJNKSYMf;";
RestConnectorMasterTable:
SQL SELECT
"__next",
"__KEY_d",
(SELECT
"candidateId",
"employeeJobTitle",
"serviceDate",
"cellPhone",
"address2",
"lastModifiedDateTime",
"homePhone",
"usersSysId",
"visibilityOption",
"publicIntranet",
"city",
"nickName",
"employeeServiceLine",
"externalCandidate",
"dateofAvailability",
"middleName",
"lastName",
"dataPrivacyId",
"primaryEmail",
"country",
"partnerSource",
"candidateLocale",
"employeeCostCenter",
"anonymized",
"partnerMemberId",
"consentToMarketing",
"privacyAcceptDateTime",
"lastLoginDateTime",
"firstName",
"candidateType",
"creationDateTime",
"zip",
"contactEmail",
"address",
"shareProfile",
"anonymizedDateTime",
"employeeNumber",
"__KEY_results",
"__FK_results",
(SELECT
"uri",
"type",
"__FK___metadata"
FROM "__metadata" FK "__FK___metadata"),
(SELECT
"__KEY_languages",
"__FK_languages",
(SELECT
"uri" AS "uri_u0",
"__FK___deferred"
FROM "__deferred" FK "__FK___deferred")
FROM "languages" PK "__KEY_languages" FK "__FK_languages"),
(SELECT
"__KEY_references",
"__FK_references",
(SELECT
"uri" AS "uri_u1",
"__FK___deferred_u0"
FROM "__deferred" FK "__FK___deferred_u0")
FROM "references" PK "__KEY_references" FK "__FK_references"),
(SELECT
"__KEY_jobsApplied",
"__FK_jobsApplied",
(SELECT
"uri" AS "uri_u2",
"__FK___deferred_u1"
FROM "__deferred" FK "__FK___deferred_u1")
FROM "jobsApplied" PK "__KEY_jobsApplied" FK "__FK_jobsApplied"),
(SELECT
"__KEY_prefix",
"__FK_prefix",
(SELECT
"uri" AS "uri_u3",
"__FK___deferred_u2"
FROM "__deferred" FK "__FK___deferred_u2")
FROM "prefix" PK "__KEY_prefix" FK "__FK_prefix"),
(SELECT
"__KEY_memberships",
"__FK_memberships",
(SELECT
"uri" AS "uri_u4",
"__FK___deferred_u3"
FROM "__deferred" FK "__FK___deferred_u3")
FROM "memberships" PK "__KEY_memberships" FK "__FK_memberships"),
(SELECT
"__KEY_candidateProfileExtension",
"__FK_candidateProfileExtension",
(SELECT
"uri" AS "uri_u5",
"__FK___deferred_u4"
FROM "__deferred" FK "__FK___deferred_u4")
FROM "candidateProfileExtension" PK "__KEY_candidateProfileExtension" FK "__FK_candidateProfileExtension"),
(SELECT
"__KEY_state",
"__FK_state",
(SELECT
"uri" AS "uri_u6",
"__FK___deferred_u5"
FROM "__deferred" FK "__FK___deferred_u5")
FROM "state" PK "__KEY_state" FK "__FK_state"),
(SELECT
"__KEY_education",
"__FK_education",
(SELECT
"uri" AS "uri_u7",
"__FK___deferred_u6"
FROM "__deferred" FK "__FK___deferred_u6")
FROM "education" PK "__KEY_education" FK "__FK_education"),
(SELECT
"__KEY_jobReqFwdCandidates",
"__FK_jobReqFwdCandidates",
(SELECT
"uri" AS "uri_u8",
"__FK___deferred_u7"
FROM "__deferred" FK "__FK___deferred_u7")
FROM "jobReqFwdCandidates" PK "__KEY_jobReqFwdCandidates" FK "__FK_jobReqFwdCandidates"),
(SELECT
"__KEY_outsideWorkExperience",
"__FK_outsideWorkExperience",
(SELECT
"uri" AS "uri_u9",
"__FK___deferred_u8"
FROM "__deferred" FK "__FK___deferred_u8")
FROM "outsideWorkExperience" PK "__KEY_outsideWorkExperience" FK "__FK_outsideWorkExperience"),
(SELECT
"__KEY_mobility",
"__FK_mobility",
(SELECT
"uri" AS "uri_u10",
"__FK___deferred_u9"
FROM "__deferred" FK "__FK___deferred_u9")
FROM "mobility" PK "__KEY_mobility" FK "__FK_mobility"),
(SELECT
"__KEY_tags",
"__FK_tags",
(SELECT
"uri" AS "uri_u11",
"__FK___deferred_u10"
FROM "__deferred" FK "__FK___deferred_u10")
FROM "tags" PK "__KEY_tags" FK "__FK_tags"),
(SELECT
"__KEY_customWorkSample",
"__FK_customWorkSample",
(SELECT
"uri" AS "uri_u12",
"__FK___deferred_u11"
FROM "__deferred" FK "__FK___deferred_u11")
FROM "customWorkSample" PK "__KEY_customWorkSample" FK "__FK_customWorkSample"),
(SELECT
"__KEY_community",
"__FK_community",
(SELECT
"uri" AS "uri_u13",
"__FK___deferred_u12"
FROM "__deferred" FK "__FK___deferred_u12")
FROM "community" PK "__KEY_community" FK "__FK_community"),
(SELECT
"__KEY_suffix",
"__FK_suffix",
(SELECT
"uri" AS "uri_u14",
"__FK___deferred_u13"
FROM "__deferred" FK "__FK___deferred_u13")
FROM "suffix" PK "__KEY_suffix" FK "__FK_suffix"),
(SELECT
"__KEY_candidateProfileConversionInfo",
"__FK_candidateProfileConversionInfo",
(SELECT
"uri" AS "uri_u15",
"__FK___deferred_u14"
FROM "__deferred" FK "__FK___deferred_u14")
FROM "candidateProfileConversionInfo" PK "__KEY_candidateProfileConversionInfo" FK "__FK_candidateProfileConversionInfo"),
(SELECT
"__KEY_certifications",
"__FK_certifications",
(SELECT
"uri" AS "uri_u16",
"__FK___deferred_u15"
FROM "__deferred" FK "__FK___deferred_u15")
FROM "certifications" PK "__KEY_certifications" FK "__FK_certifications"),
(SELECT
"__KEY_comments",
"__FK_comments",
(SELECT
"uri" AS "uri_u17",
"__FK___deferred_u16"
FROM "__deferred" FK "__FK___deferred_u16")
FROM "comments" PK "__KEY_comments" FK "__FK_comments")
FROM "results" PK "__KEY_results" FK "__FK_results")
FROM JSON (wrap off) "d" PK "__KEY_d"
//WITH CONNECTION (Url "$(vURL)")
;

[Candidate_MetaData]:
LOAD [uri],
[type],
[__FK___metadata] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK___metadata]);

[Languages]:
LOAD [__KEY_languages],
[__FK_languages] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_languages]);

[References]:
LOAD [__KEY_references],
[__FK_references] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_references]);

[JobsApplied]:
LOAD [__KEY_jobsApplied],
[__FK_jobsApplied] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_jobsApplied]);

[Prefix]:
LOAD [__KEY_prefix],
[__FK_prefix] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_prefix]);

[Memberships]:
LOAD [__KEY_memberships],
[__FK_memberships] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_memberships]);

[CandidateProfileExtension]:
LOAD [__KEY_candidateProfileExtension],
[__FK_candidateProfileExtension] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_candidateProfileExtension]);

[State]:
LOAD [__KEY_state],
[__FK_state] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_state]);

[Education]:
LOAD [__KEY_education],
[__FK_education] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_education]);

[JobReqFwdCandidates]:
LOAD [__KEY_jobReqFwdCandidates],
[__FK_jobReqFwdCandidates] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_jobReqFwdCandidates]);

[OutsideWorkExperience]:
LOAD [__KEY_outsideWorkExperience],
[__FK_outsideWorkExperience] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_outsideWorkExperience]);

[Mobility]:
LOAD [__KEY_mobility],
[__FK_mobility] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_mobility]);

[Tags]:
LOAD [__KEY_tags],
[__FK_tags] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_tags]);

[CustomWorkSample]:
LOAD [__KEY_customWorkSample],
[__FK_customWorkSample] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customWorkSample]);

[Community]:
LOAD [__KEY_community],
[__FK_community] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_community]);

[Suffix]:
LOAD [__KEY_suffix],
[__FK_suffix] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_suffix]);

[CandidateProfileConversionInfo]:
LOAD [__KEY_candidateProfileConversionInfo],
[__FK_candidateProfileConversionInfo] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_candidateProfileConversionInfo]);

[Certifications]:
LOAD [__KEY_certifications],
[__FK_certifications] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_certifications]);

[Comments]:
LOAD [__KEY_comments],
[__FK_comments] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_comments]);

Candidates:
LOAD
[candidateId] as Can_candidateId,
[employeeJobTitle] as Can_employeeJobTitle,
[serviceDate],
Mid([serviceDate],7,13) as ServiceDate,
len(trim([cellPhone])) as [CellNo_Length],
[cellPhone],
[address2] as Can_address2,
[lastModifiedDateTime],
Mid([lastModifiedDateTime],7,13) as ModifiedDate,
[homePhone] as Can_homePhone,
[usersSysId] as Can_usersSysId,
[visibilityOption] as Can_visibilityOption,
[publicIntranet] as Can_publicIntranet,
[city] as Can_city,
[nickName] as Can_nickName,
[employeeServiceLine] as Can_employeeServiceLine,
[externalCandidate] as Can_externalCandidate,
[dateofAvailability],
Mid([dateofAvailability],7,13) as AvailabilityDate,
[middleName] as Can_middleName,
[lastName] as Can_lastName,
[dataPrivacyId] as Can_dataPrivacyId,
[primaryEmail] as Can_primaryEmail,
[country] as Can_country,
[partnerSource] as Can_partnerSource,
[candidateLocale] as Can_candidateLocale,
[employeeCostCenter] as Can_employeeCostCenter,
[anonymized] as Can_anonymized,
[partnerMemberId] as Can_partnerMemberId,
[consentToMarketing] as Can_consentToMarketing,
[privacyAcceptDateTime],
Mid([privacyAcceptDateTime],7,13) as PrivacyAcceptDate,
[lastLoginDateTime],
Mid([lastLoginDateTime],7,13) as LastLoginDate,
[firstName] as Can_firstName,
lastName & ', ' & firstName as Candidate_Name,
[candidateType] as Can_candidateType,
creationDateTime,
Mid([creationDateTime],7,13) as CreateDate,
[zip] as Can_zip,
[contactEmail] as Can_contactEmail,
[address] as Can_address,
[shareProfile] as Can_shareProfile,
[anonymizedDateTime],
Mid([anonymizedDateTime],7,13) as AnonymizedDate,
[employeeNumber] as Can_employeeNumber,
[__KEY_results],
[__FK_results] AS [__KEY_d]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_results]);

STORE * FROM [RestConnectorMasterTable] into qvd\Candidates.QVD;

LET vCount = FieldValueCount('__next');
LET vURL_new = FieldValue('__next', vCount);

//Exit Do when vURL = vURL_new;

IF(vURL <> vURL_new) then
vURL = vURL_new;
ELSEIF(vURL = vURL_new) then
vURL = '';
end if

Trace $(vURL);

Loop

DROP TABLE RestConnectorMasterTable;

DROP TABLE Candidates;

EXIT SCRIPT;

Re: Exit Do Loop

Marcos_rv — Mon, 22 Apr 2019 20:53:13 GMT

y this :

LET vCount = FieldValueCount('__next');
LET vURL_new = FieldValue('__next', $(vCount));

IF($(vURL) <> $(vURL_new)) then
let vURL = $(vURL_new);
ELSEIF( $(vURL) = $(vURL_new)) then
let vURL = '';
end if

Re: Exit Do Loop

tmumaw — Tue, 23 Apr 2019 10:50:29 GMT

Because I'm reading data from SAP Successfactors and you can only retrieve 1000 rows at a time and need to get new URL each time.

topic Exit Do Loop in QlikView

Exit Do Loop

Re: Exit Do Loop

Re: Exit Do Loop