Solved: Loop - Qlik Community

ChannaK · ‎2024-05-01

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 · ‎2024-05-01

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 · ‎2024-05-01

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 · ‎2024-05-01

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 · ‎2024-05-01

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 · ‎2024-05-01

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

then only it will bring next set of record

steeefan · ‎2024-05-01

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 · ‎2024-05-01

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 · ‎2024-05-01

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

SET vMore = 'True';

ChannaK · ‎2024-05-01

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

ChannaK · ‎2024-05-01

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

Related Topics