Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
jptneumann
Partner
Partner

Pipefy and Qlik Sense Integration

Here at our company NTech, we have developed Analytics projects for customers using Pipefy (https://www.pipefy.com) and Qlik Sense.  I didn't find any reading on the topic, so I decided to talk here a little about the use of Qlik Sense to build analytical panels with Pipefy data through GraphQL based APIs.

Qlik Sense has the ability to access data from a wide list of data sources, whether structured or unstructured. To access Pipefy data via the GraphQL API, we use a REST connector, native to the tool. I even wrote an article some time ago called "GraphQL: How to use the Evolution of Web APIs in Qlik Sense" (written in Portuguese).

In this article we will cover the basic concepts, since in Pipefy we have a series of objects such as Pipes, Cards, Phases, Phases History, Labels, Tables, Records, Cards Fields, among others, as well as issues such as data pagination.

A nice thing about Qlik Sense is that we can read all these objects individually and then store them in QVD format (data file in Qlik format) and from these QVDs build the desired views integrating all objects.

Initially we need to define our data loads and logically have the access token already in hand.

1st Load: List of Pipes

We built our GraphyQL Query, according to Pipefy documentation:

 

{"query":"{ organizations { id name created_at pipes(include_publics: true) { id name } } }"}

 

Then we created a REST connection, parameterizing it according to the images below:

Inserting API URL and our GraphQL query into the Request Body:

 

f33f6805-9e22-48a0-a62f-70eb6ae84567

 

And Inserting you Token in Query Headers:

a668a2e1-e7e4-4185-bdf9-42813d3a1056

Then give your connection a name (like "pipes") and save.


Then, create another REST connection, this time to consult the Cards of our Pipe "Gestão de Trabalho Remoto". It is important to know the Pipe ID.


2nd Load: List of Pipe Cards "Gestão de Trabalho Remoto" ID 301532736

Again, we built our new GraphyQL Query, according to Pipefy documentation:

 

{"query":"{allCards(pipeId:301532736){ edges { node { id title due_date current_phase {name} pipe {id} } } } } "}

 

In this case, follow all the procedures of the 1st Load, changing only the GraphQL query in the Request Body, and save as the connection with another name (like "cards").

We can make several loads using only a single REST connection, dynamically changing the parameters in the load script, using the WITH CONNECTION statement. However, we will not cover the use of WITH CONNECTION in this article.

Then build the script for the two loads (pipes and cards), through the automatic generation of the connectors (Select Data).

4bf375f7-c427-4ab8-ab4f-e29d348bbb63

It is necessary to adjust the names of the fields so that the data model does not have conflicts and to generate the associative model correctly.

Here are the scripts for the 2 loads:

Pipe Script:

 

/*
'
LIB CONNECT TO 'Pipes';

RestConnectorMasterTable:
SQL SELECT 
    "__KEY_data",
    (SELECT 
        "id" AS "id_u0",
        "name" AS "name_u0",
        "created_at",
        "__KEY_organizations",
        "__FK_organizations",
        (SELECT 
            "id",
            "name",
            "__FK_pipes"
        FROM "pipes" FK "__FK_pipes")
    FROM "organizations" PK "__KEY_organizations" FK "__FK_organizations")
FROM JSON (wrap off) "data" PK "__KEY_data";

[organizations]:
LOAD    
    [id_u0] AS organization_id,
    [name_u0] AS organization_name,
    [created_at] AS organization_created_at,
    [__KEY_organizations],
    [__FK_organizations] AS [__KEY_data]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_organizations]);

[pipes]:
LOAD    
    [id] as pipe_id,
    [name] as pipe_name,
    [__FK_pipes] AS [__KEY_organizations]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_pipes]);
Left Join(pipes)
LOAD 
    organization_id,
    organization_name,
    [__KEY_organizations]
RESIDENT [organizations];

DROP TABLE RestConnectorMasterTable;
DROP TABLE [organizations];
DROP Field [__KEY_organizations] From [pipes];
'
*/

 

Cards Script:

 

/*
LIB CONNECT TO 'Cards';

RestConnectorMasterTable:
SQL SELECT 
    "__KEY_data",
    (SELECT 
        "__KEY_allCards",
        "__FK_allCards",
        (SELECT 
            "__KEY_edges",
            "__FK_edges",
            (SELECT 
                "id" AS "id_u0",
                "title",
                "due_date",
                "__KEY_node",
                "__FK_node",
                (SELECT 
                    "name",
                    "__FK_current_phase"
                FROM "current_phase" FK "__FK_current_phase"),
                (SELECT 
                    "id",
                    "__FK_pipe"
                FROM "pipe" FK "__FK_pipe")
            FROM "node" PK "__KEY_node" FK "__FK_node")
        FROM "edges" PK "__KEY_edges" FK "__FK_edges")
    FROM "allCards" PK "__KEY_allCards" FK "__FK_allCards")
FROM JSON (wrap off) "data" PK "__KEY_data";

[cardpipe]:
LOAD    
    [id] as pipe_id,
    [__FK_pipe] AS [__KEY_node]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_pipe]);

[current_phase]:
LOAD    
    [name] as current_phase_name,
    [__FK_current_phase] AS [__KEY_node]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_current_phase]);


[cards]:
LOAD    
    [id_u0] as card_id,
    [title] as card_title,
    [due_date] as card_due_date,
    [__KEY_node]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_node]);
Left Join(cards)
LOAD 
    current_phase_name,
    [__KEY_node]
RESIDENT [current_phase];
Left Join(cards)
LOAD 
    pipe_id,
     [__KEY_node]
RESIDENT [cardpipe];

DROP TABLE RestConnectorMasterTable;
DROP Table [cardpipe];
DROP TABLE [current_phase];
DROP Field  [__KEY_node]from cards;
*/

 

Once the data model is loaded, it will look like this:

167b6a6f-6ed9-42ae-a37e-1761275f80c4

And finally we can make our panels, like the example below:

14408d7a-5b20-4d24-a001-3c195a7cc809

That's it! See you the next!

Labels (1)
  • Other

1 Solution

Accepted Solutions
Dalton_Ruer
Employee
Employee

Just so you know I pressed like 3 times. Extra credit for using Emojis in your visuals to make things really engaging. 

View solution in original post

2 Replies
Dalton_Ruer
Employee
Employee

Just so you know I pressed like 3 times. Extra credit for using Emojis in your visuals to make things really engaging. 

View solution in original post

sandra_romero
Partner
Partner

Thaks for the information, do you have any example using  WITH CONNECTION ??