PQL and Python

[ ]:
#To get a copy of this notebook in your current working dirtectory, run:
from pycelonis import notebooks

This tutorial shows how to: * Extract the PQL query from an existing OLAP table from Celonis * Add a PQL column to that table * Add a filter to that table

To do the tasks mentioned above we first need to connect to Celonis and connect to an analysis.

[1]:
from pycelonis import get_celonis

celonis = get_celonis()
analysis = celonis.analyses.find('440cb0ba-7f00-4ec9-a50a-f9f8772e5893')
2019-10-08 12:14:05 - Login successful! Hello Simon Riezebos

Extract the PQL query from an existing OLAP table from Celonis

The table in the analysis contains two columns from the LFA1 table. The columns are the vendors and the vendors’ countries.

We first need to find the table in the analysis and extract its PQL-query. The title of the OLAP table is “Vendors”.

[2]:
query = analysis.draft.components.find("Vendors").pql_query
No exact match, guessed: <BaseAnalysisComponent, id 6bce2ff5-ee25-4d59-8387-81c387a8fc30, name #{Vendors}>

Let’s first have a look at the PQL query. The table’s query consists of two PQL columns, there are no filters on the table and the table does not have any variables.

[3]:
query.columns
[3]:
[<PQLColumn, name: LFA1.LIFNR, query: "LFA1"."LIFNR">,
 <PQLColumn, name: LFA1.LAND1, query: "LFA1"."LAND1">]
[4]:
query.filters
[4]:
[]
[5]:
query.variables
[5]:

If we want to extract the table from the analysis we can do that by passing the query as an argument to the get_data_frame function.

[6]:
df = analysis.get_data_frame(query)
df.head()
[6]:
LFA1.LIFNR LFA1.LAND1
0 SORAYA45 ES
1 SP-CARA IT
2 SP-CARB IT
3 SRV-2 DE
4 SS_FLEET1 US

Add a PQL column to that table

In this section, we add a column to the PQL query and extract the table again with the new column. For this, we need to import pycelonis.pql

[7]:
from pycelonis.pql import PQLColumn

Create a PQL column object. Here we want, additionally to the vendor’s number and country, the city of the vendor. The city can be found in the column ORT01 of the LFA1 table. It is very important here that we use the exact same syntax (“LFA1”.“ORT01”) as we would use it in Celonis. Common errors that could happen here are: * Missing double quotes e.g. LFA1“.”ORT01” * Spelling mistakes of the table’s name e.g. “LFA”.“ORT01” * Spelling mistakes in the table’s column e.g. “LFA1”.“ORT0”

The errors will occur first when we pull the data. In case you get an error message there, first check the spelling of your PQL query

[8]:
column = PQLColumn(query = '"LFA1"."ORT01"', name= "City", sorting_index = 3, sorting_direction = 'DESC')

Now we add the column to the query we extracted previously

[9]:
query.add(column)

The column is added to the PQL query and we can extract the table now with the additional column “LFA1”.“ORT01”

[10]:
df = analysis.get_data_frame(query)
df.head()
[10]:
LFA1.LIFNR LFA1.LAND1 City
0 0000000030 ES santander
1 0000000290 ES santander
2 RFDC US refcity
3 0000009907 DE rankfurt
4 0000005001 JP osaka

Add a PQL filter to that table

The next step now is to add a filter to the analysis. In this case we are only interested in Vendors from Italy (“LFA1”.“LAND1” = ‘IT’). Again, it is very important to use the same syntax as within Celonis. To avoid issues with the quotations in Python use tripple double quotes “”” “””

[11]:
from pycelonis.pql import PQLFilter
[12]:
pql_filter = PQLFilter(""" "LFA1"."LAND1" = 'IT' """)

Now add the filter to the query …

[13]:
query.add(pql_filter)

… and extract the filtered table.

[14]:
df = analysis.get_data_frame(query)
df.head()
[14]:
LFA1.LIFNR LFA1.LAND1 City
0 VS333 IT Torino
1 0000077101 IT Rome
2 D200000 IT Roma
3 VS335 IT Napoli
4 SP-CARB IT Milano