PQL and Python¶

In [1]:

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

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

1. Connect to Celonis¶

In [2]:

from pycelonis import get_celonis
celonis = get_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".

In [3]:

analysis = celonis.analyses.find('440cb0ba-7f00-4ec9-a50a-f9f8772e5893')
query = analysis.draft.components.find("Vendors").pql_query

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.

In [4]:

query.columns

query.columns

Out[4]:

[<PQLColumn, name: LFA1.LIFNR, query: "LFA1"."LIFNR">,
 <PQLColumn, name: LFA1.LAND1, query: "LFA1"."LAND1">]

In [5]:

query.filters

query.filters

Out[5]:

[]

In [6]:

query.variables

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.

In [7]:

df = analysis.get_data_frame(query)
df.head()

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

In [8]:

from pycelonis.celonis_api.pql.pql import PQLColumn

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

In [9]:

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

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"

In [10]:

query.add(column)

query.add(column)

In [11]:

df = analysis.get_data_frame(query)
df.head()

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 """ """

In [12]:

from pycelonis.celonis_api.pql.pql import PQLFilter

from pycelonis.celonis_api.pql.pql import PQLFilter

In [13]:

pql_filter = PQLFilter(""" "LFA1"."LAND1" = 'IT' """)

pql_filter = PQLFilter(""" "LFA1"."LAND1" = 'IT' """)

Now add the filter to the query ...

In [14]:

query.add(pql_filter)

query.add(pql_filter)

... and extract the filtered table.

In [15]:

df = analysis.get_data_frame(query)
df.head()

df = analysis.get_data_frame(query) df.head()

Out[15]:

	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