Solved: Re: Loop - Page 2 - Qlik Community

ChannaK

I am trying to download data from hubspot

loop is not getting all only first set

every time i need to pass output value to URL using this variable (vRowCount)

i test with postman

////////////below my script

LIB CONNECT TO 'HubspotContacts'

do while vMore = 'True';

Trace($(vRowCount));

RestConnectorMasterTable:

SQL SELECT

"has-more",

"vid-offset",

"__KEY_root",

(SELECT

"vid" AS "vid_u0",

"canonical-vid",

"portal-id" AS "portal-id_u0",

"is-contact",

"addedAt",

"__KEY_contacts",

"__FK_contacts",

(SELECT

"__KEY_properties",

"__FK_properties",

(SELECT

"value",

"__FK_hs_email_last_send_date"

FROM "hs_email_last_send_date" FK "__FK_hs_email_last_send_date"),

(SELECT

"value" AS "value_u0",

"__FK_lastmodifieddate"

FROM "lastmodifieddate" FK "__FK_lastmodifieddate"),

(SELECT

"value" AS "value_u1",

"__FK_hs_email_delivered"

FROM "hs_email_delivered" FK "__FK_hs_email_delivered"),

(SELECT

"value" AS "value_u2",

"__FK_salesforcecontactid"

FROM "salesforcecontactid" FK "__FK_salesforcecontactid"),

(SELECT

"value" AS "value_u3",

"__FK_salesforceaccountid"

FROM "salesforceaccountid" FK "__FK_salesforceaccountid")

FROM "properties" PK "__KEY_properties" FK "__FK_properties")

FROM "contacts" PK "__KEY_contacts" FK "__FK_contacts")

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION(Url "https://api.hubapi.com/contacts/v1/lists/all/contacts/all?property=hs_email_delivered&property=sales...)",

HTTPHEADER "Authorization" "Bearer ABABABABABA");

[hs_email_last_send_date]:

LOAD [value],

[__FK_hs_email_last_send_date] AS [__KEY_properties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_hs_email_last_send_date]);

[lastmodifieddate]:

LOAD [value_u0] AS [value_u0],

[__FK_lastmodifieddate] AS [__KEY_properties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_lastmodifieddate]);

[hs_email_delivered]:

LOAD [value_u1] AS [value_u1],

[__FK_hs_email_delivered] AS [__KEY_properties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_hs_email_delivered]);

[salesforcecontactid]:

LOAD [value_u2] AS [value_u2],

[__FK_salesforcecontactid] AS [__KEY_properties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_salesforcecontactid]);

[salesforceaccountid]:

LOAD [value_u3] AS [value_u3],

[__FK_salesforceaccountid] AS [__KEY_properties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_salesforceaccountid]);

[properties]:

LOAD [__KEY_properties],

[__FK_properties] AS [__KEY_contacts]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_properties]);

[contacts]:

LOAD [vid_u0] AS [vid_u0],

[canonical-vid],

[portal-id_u0] AS [portal-id_u0],

[is-contact],

[addedAt],

[__KEY_contacts],

[__FK_contacts] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_contacts]);

[root]:

LOAD [has-more] ,

[vid-offset] ,

[__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__KEY_root]);

let vMore =peek('has-more',0,'root');

let vRowCount =peek('vid-offset',0,'root');

DROP TABLE RestConnectorMasterTable;

loop

steeefan

As I posted earlier: Before entering the loop, you have to initiate the variable:

SET vMore = 'True';

In your case:

SET vMore = 'True';
DO WHILE vMore = 'True';
  // Your code
  LET vMore = peek('has-more',-1,'root');
  LET vRowCount = peek('vid-offset',-1,'root');
LOOP;

Loop

Script