Solved: Re: Loop - 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;

View solution in original post

steeefan

You need to define RestConnectorMasterTable outside of your loop, then keep adding to that table. The breakdown of the table, i.e. the creation of the hs_email_last_send_date, hs_email_delivered etc. will then happen after the loop has finished.

LIB CONNECT TO 'HubspotContacts'

RestConnectorMasterTable:
NOCONCATENATE LOAD * INLINE [
  has-more
];

do while vMore = 'True';
  Trace($(vRowCount));
 
  CONCATENATE (RestConnectorMasterTable) LOAD
    *;
  SQL SELECT 
    "has-more",
    "vid-offset",
    "__KEY_root",
    //..
        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");

  let vMore =peek('has-more',0,'root');
  let vRowCount =peek('vid-offset',0,'root');
loop
 
[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]);

//..

[root]:
LOAD
  [has-more] ,
  [vid-offset] ,
  [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);

DROP TABLE RestConnectorMasterTable;

ChannaK

Hi @steeefan

Thank you very much for response

i tried with your script i get below error

Field '__FK_hs_email_last_send_date' not found

The error occurred here:

[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])

here root is my table name but you added on the top not sure, i try adding bottom also

i need to repeat loop until this column has-more = false

  let vMore =peek('has-more',0,'root');
  let vRowCount =peek('vid-offset',0,'root');
loop

//////below updted script

LIB CONNECT TO 'HubspotContacts';

RestConnectorMasterTable:

NOCONCATENATE LOAD * INLINE [

has-more

];

do while vMore = 'True';

Trace($(vRowCount));

CONCATENATE (RestConnectorMasterTable) LOAD

*;

SQL SELECT

"has-more",

"vid-offset",

"__KEY_root",

(SELECT

"__FK_contacts",

"__KEY_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 ABABABA");

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

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

loop

[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]);

[root]:

LOAD [has-more],

[vid-offset],

[__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__KEY_root]);

DROP TABLE RestConnectorMasterTable;

steeefan

My script is not complete and will not run. It's a simplifiation and contraction of the script you posted to illustrate my suggestion. You need to use your actual REST script within the loop that I showed.

ChannaK

i need to pass this variable value to the URL "vRowCount"

then only it will bring next set of record

steeefan

Then make sure to add the variable $(vRowCount) to the URL of your request. See the HubSpot API documentation on how to achieve this.

ChannaK

Hi @steeefan Thank you for response

i added to URL

&vidOffset=$(vRowCount)"

the problem loop is not working based on other column (valriable)

loop need to repeat until has-more = False

steeefan

Right, I see. Before entering the loop, you have to initiate the variable:

SET vMore = 'True';

ChannaK

Yes @steeefan initially it should be True and later it will read value from output

ChannaK

Hi @steeefan

i try using for loop

like below it works but i need while loop to stop the loop

can you suggest

LIB CONNECT TO 'HubspotContacts';

for i = 0 to 5;

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

"@Value",

"__FK_merged-vids"

FROM "merged-vids" FK "__FK_merged-vids" ArrayValueAlias "@Value"),

(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"),

(SELECT

"conversion-id",

"timestamp",

"form-id",

"portal-id",

"page-url",

"page-title",

"title",

"form-type",

"content-type",

"page-id",

"__KEY_form-submissions",

"__FK_form-submissions",

(SELECT

"@Value" AS "@Value_u0",

"__FK_contact-associated-by"

FROM "contact-associated-by" FK "__FK_contact-associated-by" ArrayValueAlias "@Value_u0"),

(SELECT

"@Value" AS "@Value_u1",

"__FK_meta-data"

FROM "meta-data" FK "__FK_meta-data" ArrayValueAlias "@Value_u1")

FROM "form-submissions" PK "__KEY_form-submissions" FK "__FK_form-submissions"),

(SELECT

"vid",

"saved-at-timestamp",

"deleted-changed-timestamp",

"__KEY_identity-profiles",

"__FK_identity-profiles",

(SELECT

"type",

"value" AS "value_u4",

"timestamp" AS "timestamp_u0",

"is-primary",

"__FK_identities"

FROM "identities" FK "__FK_identities")

FROM "identity-profiles" PK "__KEY_identity-profiles" FK "__FK_identity-profiles"),

(SELECT

"@Value" AS "@Value_u2",

"__FK_merge-audits"

FROM "merge-audits" FK "__FK_merge-audits" ArrayValueAlias "@Value_u2")

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 ABABABABA");

[merged-vids]:

LOAD [@Value],

[__FK_merged-vids] AS [__KEY_contacts]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_merged-vids]);

[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]);

[contact-associated-by]:

LOAD [@Value_u0] AS [@Value_u0],

[__FK_contact-associated-by] AS [__KEY_form-submissions]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_contact-associated-by]);

[meta-data]:

LOAD [@Value_u1] AS [@Value_u1],

[__FK_meta-data] AS [__KEY_form-submissions]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_meta-data]);

[form-submissions]:

LOAD [conversion-id],

[timestamp],

[form-id],

[portal-id],

[page-url],

[page-title],

[title],

[form-type],

[content-type],

[page-id],

[__KEY_form-submissions],

[__FK_form-submissions] AS [__KEY_contacts]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_form-submissions]);

[identities]:

LOAD [type],

[value_u4] AS [value_u4],

[timestamp_u0] AS [timestamp_u0],

[is-primary],

[__FK_identities] AS [__KEY_identity-profiles]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_identities]);

[identity-profiles]:

LOAD [vid],

[saved-at-timestamp],

[deleted-changed-timestamp],

[__KEY_identity-profiles],

[__FK_identity-profiles] AS [__KEY_contacts]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_identity-profiles]);

[merge-audits]:

LOAD [@Value_u2] AS [@Value_u2],

[__FK_merge-audits] AS [__KEY_contacts]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_merge-audits]);

[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',-1,'root');

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

DROP TABLE RestConnectorMasterTable;

next i;

Loop

Script