Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

Exit Do Loop

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;

Labels (1)
2 Replies
Highlighted
Creator II
Creator II

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

Highlighted
Specialist
Specialist

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.