PQL and Python¶
#To get a copy of this notebook in your current working dirtectory, run:
from pycelonis.notebooks import api_tutorial
1. Connect to Celonis¶
from pycelonis import get_celonis
celonis = get_celonis()
2. 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".
analysis = celonis.analyses.find('440cb0ba-7f00-4ec9-a50a-f9f8772e5893')
query = analysis.draft.components.find("Vendors").pql_query
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.
query.columns
[<PQLColumn, name: LFA1.LIFNR, query: "LFA1"."LIFNR">, <PQLColumn, name: LFA1.LAND1, query: "LFA1"."LAND1">]
query.filters
[]
query.variables
3. Extract the Data from an OLAP Table¶
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.
df = analysis.get_data_frame(query)
df.head()
4. 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
from pycelonis.celonis_api.pql.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
column = PQLColumn(query = '"LFA1"."ORT01"', name= "City", sorting_index = 3, sorting_direction = 'DESC')
5. Add the column to the query we extracted previously¶
The column is added to the PQL query and we can extract the table now with the additional column "LFA1"."ORT01"
query.add(column)
df = analysis.get_data_frame(query)
df.head()
6. 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 """ """
from pycelonis.celonis_api.pql.pql import PQLFilter
pql_filter = PQLFilter(""" "LFA1"."LAND1" = 'IT' """)
Now add the filter to the query ...
query.add(pql_filter)
... and extract the filtered table.
df = analysis.get_data_frame(query)
df.head()
| 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 |