Iterative Macro to fetch 20k Records

HI All,

I need the community expertise's.

Overview:

I have a workflow where i use a Graphyql Query
I have been successful at obtaining a single JSON string that I then parse into a table.
I know the total number of records that my request needs to iterate through.

Problem:

I know how to pass values from my query to get desired results but I don't know how to create a loop to retrieve all the records
At the moment within the GraphQl Query i have applied a pagecontrol to grab the first record, If i change this pagecontrol filter to Example 2000 then am presented with a "504 Gateway Time - out" issue

Graphql query

query{
  getAllCompanies(companyLevel : "Company", pageControl:{first:2}){
  totalCount
    pageInfo {
      endCursor
      hasNextPage
      startCursor
      hasPreviousPage
    }
    edges {
      cursor
      node {
        companyId  
        name
        }
      }
    }
  }

Graphql Results

{
  "data": {
    "getAllCompanies": {
      "totalCount": 20000,
      "pageInfo": {
        "endCursor": "Q0RNQzAwNDF4NE90ak54WUM0bDE=",
        "hasNextPage": true,
        "startCursor": "Q0RNQzAwM2lBdThjYWc4UGR3cjU=",
        "hasPreviousPage": false
      },
      "edges": [
        {
          "cursor": "Q0RNQzAwM2lBdThjYWc4UGR3cjU=",
          "node": {
            "companyId": "CDMC003iAu8cag8Pdwr5",
            "name": "ABC Testng"
          }
        },
        {
          "cursor": "Q0RNQzAwNDF4NE90ak54WUM0bDE=",
          "node": {
            "companyId": "CDMC0041x4OtjNxYC4l1",
            "name": "Test 1"
          }
        }
      ]
    }
  }
}

Graphql Query Within alteryx

'{"query":"query{\n  getAllCompanies(companyLevel : \"Company\", pageControl:{first:2}){\n  totalCount\n    pageInfo {\n      endCursor\n      hasNextPage\n      startCursor\n      hasPreviousPage\n    }\n    edges {\n      cursor\n      node {\n        companyId  \n        name\n        }\n      }\n    }\n  }\n"}'

Alteryx DownloadData (Download Tool)

{"data":{"getAllCompanies":{"totalCount":20000,"pageInfo":{"endCursor":"Q0RNQzAwNDF4NE90ak54WUM0bDE=","hasNextPage":true,"startCursor":"Q0RNQzAwM2lBdThjYWc4UGR3cjU=","hasPreviousPage":false},"edges":[{"cursor":"Q0RNQzAwM2lBdThjYWc4UGR3cjU=","node":{"co...

Json Parse Results

GraphQL	JSON_Name	JSON_ValueString
{"query":"query{\n getAllCompanies(companyLevel : \"Company\", pageControl:{first:2}){\n totalCount\n pageInfo {\n endCursor\n hasNextPage\n startCursor\n hasPreviousPage\n }\n edges {\n cursor\n node {\n ...	data.getAllCompanies.totalCount	20000
{"query":"query{\n getAllCompanies(companyLevel : \"Company\", pageControl:{first:2}){\n totalCount\n pageInfo {\n endCursor\n hasNextPage\n startCursor\n hasPreviousPage\n }\n edges {\n cursor\n node {\n ...	data.getAllCompanies.pageInfo.endCursor	Q0RNQzAwNDF4NE90ak54WUM0bDE=
{"query":"query{\n getAllCompanies(companyLevel : \"Company\", pageControl:{first:2}){\n totalCount\n pageInfo {\n endCursor\n hasNextPage\n startCursor\n hasPreviousPage\n }\n edges {\n cursor\n node {\n ...	data.getAllCompanies.pageInfo.hasNextPage	1
{"query":"query{\n getAllCompanies(companyLevel : \"Company\", pageControl:{first:2}){\n totalCount\n pageInfo {\n endCursor\n hasNextPage\n startCursor\n hasPreviousPage\n }\n edges {\n cursor\n node {\n ...	data.getAllCompanies.pageInfo.startCursor	Q0RNQzAwM2lBdThjYWc4UGR3cjU=
{"query":"query{\n getAllCompanies(companyLevel : \"Company\", pageControl:{first:2}){\n totalCount\n pageInfo {\n endCursor\n hasNextPage\n startCursor\n hasPreviousPage\n }\n edges {\n cursor\n node {\n ...	data.getAllCompanies.pageInfo.hasPreviousPage	0
{"query":"query{\n getAllCompanies(companyLevel : \"Company\", pageControl:{first:2}){\n totalCount\n pageInfo {\n endCursor\n hasNextPage\n startCursor\n hasPreviousPage\n }\n edges {\n cursor\n node {\n ...	data.getAllCompanies.edges.0.cursor	Q0RNQzAwM2lBdThjYWc4UGR3cjU=
{"query":"query{\n getAllCompanies(companyLevel : \"Company\", pageControl:{first:2}){\n totalCount\n pageInfo {\n endCursor\n hasNextPage\n startCursor\n hasPreviousPage\n }\n edges {\n cursor\n node {\n ...	data.getAllCompanies.edges.0.node.companyId	CDMC003iAu8cag8Pdwr5
{"query":"query{\n getAllCompanies(companyLevel : \"Company\", pageControl:{first:2}){\n totalCount\n pageInfo {\n endCursor\n hasNextPage\n startCursor\n hasPreviousPage\n }\n edges {\n cursor\n node {\n ...	data.getAllCompanies.edges.0.node.name	ABC Testng
{"query":"query{\n getAllCompanies(companyLevel : \"Company\", pageControl:{first:2}){\n totalCount\n pageInfo {\n endCursor\n hasNextPage\n startCursor\n hasPreviousPage\n }\n edges {\n cursor\n node {\n ...	data.getAllCompanies.edges.1.cursor	Q0RNQzAwNDF4NE90ak54WUM0bDE=
{"query":"query{\n getAllCompanies(companyLevel : \"Company\", pageControl:{first:2}){\n totalCount\n pageInfo {\n endCursor\n hasNextPage\n startCursor\n hasPreviousPage\n }\n edges {\n cursor\n node {\n ...	data.getAllCompanies.edges.1.node.companyId	CDMC0041x4OtjNxYC4l1
{"query":"query{\n getAllCompanies(companyLevel : \"Company\", pageControl:{first:2}){\n totalCount\n pageInfo {\n endCursor\n hasNextPage\n startCursor\n hasPreviousPage\n }\n edges {\n cursor\n node {\n ...	data.getAllCompanies.edges.1.node.name	Test 1

Looking at above, looks like i have to use the ID of "endCursor" , to find the next "Cursor" , to then then get the next set of data attributes

However i am confused on how the iteration macro would work, and to use the Cursor to get the next set of data

Looking forward to your response

API

Download

Iterative Macro

Accepted answers

SPetrie

I dont use graphql so Im just going by their documentation.

The graphgl.org site gives the example like this - friends(first:2 after:$friendCursor)

After the initial call, if there are pages left you would update the query to pull the next items after the end cursor.

That would pull the next 2 items after the indicated cursor based on the first:2 portion of the query.

I believe that means your new query would look something like getAllCompanies(companyLevel : "Company", pageControl:{first:2 after:Q0RNQzAwNDF4NE90ak54WUM0bDE=){.....................

Each iteration that has pages left would update the query to have the new endcursor id and send it back through for another pass.

I updated my example macro to fake one pass of that as an example.

Hopefully that can help.

test iterate2.yxmc

All comments

SPetrie

This is a mockup of an iterative macro that may work for you. Im faking the input/output of a download tool since I dont have an api with pagination available to test with.

The overall method is to send your initial url to the download tool and send the output to the output tool.

You also test for pagination, and if present, adjust your url as necessary and send it back to be put through the download tool again. Repeat until pagination is completed or max number of iterations is reached.

I bring in the original url with fake 'param1'

It generates an output that is passed to the final output anchor.

It also tests the "pagination" field for value of 1. If is has 1,it updates the url to be param2 etc. and sends it back through the macro.

It keeps going until its gets to a 0 for pagination. I have it hard coded to create that situation at iterationnumber >5 so it will produce 7 outputs.

Assuming you are correct that you can use the hasNextPage field to determine if you need to send it through again, something like this should work.

Masond3

@SPetrie I have updated the question with additional details of query structure along with the Data being returned.

SPetrie

I dont use graphql so Im just going by their documentation.

The graphgl.org site gives the example like this - friends(first:2 after:$friendCursor)

After the initial call, if there are pages left you would update the query to pull the next items after the end cursor.

That would pull the next 2 items after the indicated cursor based on the first:2 portion of the query.

I believe that means your new query would look something like getAllCompanies(companyLevel : "Company", pageControl:{first:2 after:Q0RNQzAwNDF4NE90ak54WUM0bDE=){.....................

Each iteration that has pages left would update the query to have the new endcursor id and send it back through for another pass.

I updated my example macro to fake one pass of that as an example.

Hopefully that can help.

test iterate2.yxmc

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703