Zendesk - Loop with variable

[Jorge_FR]

Hi everyone,

I'm currently working on a project that involves interacting with the Zendesk API, and I've run into a bit of an issue. I need to retrieve specific fields from a range of tickets. My code looks like this:

Let vTicketId = 0;

For vTicketId = 1 to 2    // is about 1000 tickets

RestConnectorMasterTable:

SQL SELECT 

"ticket_id",

"__KEY_ticket_metric",

(SELECT 

"calendar",

"business",

"__FK_reply_time_in_minutes"

FROM "reply_time_in_minutes" FK "__FK_reply_time_in_minutes")

FROM JSON (wrap off) "ticket_metric" PK "__KEY_ticket_metric"

WITH CONNECTION(

Url "https://company.zendesk.com/api/v2/tickets/$(vTicketId)/metrics",

HTTPHEADER "Accept" "application/json"

);

Next vTicketId;

[reply_time_in_minutes]:

LOAD

[calendar],

[business],

[__FK_reply_time_in_minutes] AS [__KEY_ticket_metric]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_reply_time_in_minutes]);

[ticket_metric]:

LOAD

[ticket_id],

[__KEY_ticket_metric]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__KEY_ticket_metric]);

DROP TABLE RestConnectorMasterTable;

My expectation was to obtain, for each ticket number, two fields (calendar, business). However, I'm encountering an issue where I'm getting all the combinations for each ticket, which seems like a concatenate table:

ticket_id calendar business
1 46665 46665
2 46665 46665
1 74941 74941
2 74941 74941

Can someone help me understand what might be going wrong here? Any advice or insights would be greatly appreciated. Thank you in advance for your assistance.

[HeshamKhja1]

Hi @Jorge_FR ,

If you load the first part of the code which is in the loop, do you get the expected result? or it is also incorrect?

Because when I look at the SQL query calling the API it seems like it is doing a join in the SQL. Is this the script Qlik generated? or you wrote it yourself?

[Jorge_FR · Accepted solution]

Hello @HeshamKhja1 

Many thanks for replying my post. The code is generated by the connector.

Here the first part of the script:

//For each ticketID In FieldValueList('ticket_id_key')

Let vTicketId = ticketID;

Let vTicketId = 0;

For vTicketId = 1 to 2

Let vURL = 'https://xxxx.zendesk.com/api/v2/tickets/'& vTicketId &'/metrics';

Trace $(vURL);

RestConnectorMasterTable:

SQL SELECT 

"url",

"ticket_id",

"__KEY_ticket_metric",

(SELECT 

"calendar",

"business",

"__FK_reply_time_in_minutes"

FROM "reply_time_in_minutes" FK "__FK_reply_time_in_minutes")

FROM JSON (wrap off) "ticket_metric" PK "__KEY_ticket_metric"

WITH CONNECTION(

Url "$(vURL)",

HTTPHEADER "Accept" "application/json"

);

// DROP TABLE RestConnectorMasterTable;

Next ;

The table is shown in the attached png file.

[Jorge_FR]

Hello,

I have resolved this issue by setting up the cursor pagination mode. Here's an example of the beginning of the loop :

For startAt = 0 to $(vTotal) step $(vPageSize)

// l'URL avec la pagination
Let vURL = 'https://company.zendesk.com/api/v2/tickets.json?page[size]=' & $(vPageSize);
trace $(vURL);

If vAfterCursor <> '' then
Let vURL = '$(vURL)&page[after]=$(vAfterCursor)';
trace vURL = $(vURL);
EndIf