Skip to main content

Data Ingestion & Infrastructure

Discuss and ask questions about data integration, task mining, admin settings, and PQL extractors to optimize your infrastructure.

  • 801 Topics
  • 2,108 Replies
801 Topics
.
Level 3
.14600782Level 3
asked in Data Ingestion & Infrastructure
SQL automatic addition of empty lines although WHERE condition

Hi everyone,I hope you can help me with my SQL Problem.I created the Table Cases and selected my Case Key and a specific filtering for Specific Type.The strange thing is that my output includes two lines but in my row data the conditions are matched for Text1.DROP TABLE IF EXISTS CASES;CREATE TABLE CASES AS(SELECT DISTINCTB.ID AS CASE_KEY,CASEWHEN B.EX = 5 AND B.VW = 4THEN Text1ELSE nextEND AS Specific TypeFROM Data AS B );SELECT * FROM CASES WHERE CASES.CASE_KEY =722OUPUT:CASE_KEY Specific Type"722 Text1722 nextDoes someone know how why I still get next as a output and how can I change the syntax to get sure to get out only Text1?

00
D
Level 1
mukesh.gupta11
Level 4
mukesh.gupta11Level 4
asked in Data Ingestion & Infrastructure
Subtract Minutes from a datetime

Is it possible to subtract minutes from a datetime and get subtracted datetime?

10
mukesh.gupta11
Level 4
.
Level 2
.14600779Level 2
asked in Data Ingestion & Infrastructure
Sum of PO Amount on each activitity on the Process Explorer

Hi - I have two tables ACTIVITY_1 and CASE_1. ACTIVITY_1 has different statuses of PO.Case_1 has all the distinct POs and PO Amount. Now I want to show the sum of PO Amount on each activitity on the Process Explorer. When I use a simple SUM calculation or PU_SUM calculation, it double counts the PO amount, if a PO has gone through an Acitivy/Status more than once. This is supposedly a simple calculation, but I dont seem to be able to create it yet. Can you help please?

00
.
Level 2
.
Level 2
.14600841Level 2
asked in Data Ingestion & Infrastructure
Rework Analysis by case keys in column chart

Hello Team,We are trying to calculate the rework cases for activity in different approach i.e in column chart where we need if case key has executed more than once , twice or thrice for particular activity then each cases should be unique in each column logicfor eg:-More than once case keys executed should not repeat in more than twice column and same with thrice column.Below is the logic we are looking for to calculate the same but not able to get unique records .greater than 1 =Cases should not repeat in >2 and >3 columngreater than 2=Cases should not repeat in >1 and >3 columngreater than 3=Cases should not repeat in >2, >1 columnKindly let me know how best we can create the logic , awaiting for your response.

60
joos.buijs
Level 6
S
Level 3
stefan.hofma12Level 3
asked in Data Ingestion & Infrastructure
Case Count of bundled activities (last occur)

Hey everyone, I am looking for a solution for the following issue. I have a bundle of 5 different activities. I need to know the count of cases for every activity.The Problem is that more of one of the bundled activities can occur in one case. Therefore I would like to count only the last occurred activity in the bundle. The required activities can occur somewhere in the process flow.Example picture attached.bundle953551 57.8 KBIs there someone who can help me to solve this problem?

20
S
Level 3
S
Level 3
stefan.hofma12Level 3
asked in Data Ingestion & Infrastructure
Join functionality - how analytically find the error?

Hello, I use a data model that has been working well for a long time. Data records are added at regular intervals (weekly). Now I got in a few components the following error message: No common parent between table could be found - please check your schema. The Tables [Table1.DATA] and [Table2.DATA] are connected, but have no common parent table. Join path [Table1]N 1![CASES]!1>N[Table2]. For more information on the join path, search for Join functionality in PQL documentation. I already checked the fields that are linked in the data model. [Table1.Data].[Field] = MULTIPLE keys [CASE].[key] = UNIQUE key [Table2.Data].[Field] = UNIQUE key I checked this with the following code: SELECT Field, COUNT(Field) AS Anzahl FROM Table2 GROUP BY Field HAVING (COUNT(Field)>1); Does anyone have an idea how the analysis of the error can be advanced here? Is it really only due to one of the tables described in the error or is it possibly due to a different table in the data model? Why is the tab

70
S
Level 3
.
Level 3
.14600843Level 3
asked in Data Ingestion & Infrastructure
Filter application to display maximum value for each column value

I want to apply a filter to display only the maximum value for each team_resolve column value.problem.xlsx (9.0 KB)

20
.
Level 3
M
Level 3
marco.messnLevel 3
asked in Data Ingestion & Infrastructure
Table or Activity Condition

Dear All i would like to build a KPI based on an OR condition But somehow the result is more than 100% how can i combine that per case? thank youSUM(CASE WHEN EKPO.LABNR = 1 OR CEL_P2P_ACTIVITIES.ACTIVITY_EN = Create Order Confirmation THEN 1.0 ELSE 0.0 END)/COUNT_TABLE(EKPO)

10
D
Level 2
.
Level 3
.14600843Level 3
asked in Data Ingestion & Infrastructure
Get the second last status for each case

In my activities table, I have activities like set status and change status. I want to calculate what is second last status value for each case, the status values are given in the changed_to column next to the activity column

50
.
Level 3
.
Level 2
.14600847Level 2
asked in Data Ingestion & Infrastructure
How to get all data model name using python

Hi All, Im try to get all my data model list in my celonis application, for the purpose migrating to prd to UAT etc is there any syntax in python to get the list of DM. Thanks,

10
.
Level 2
T
Level 1
tomas.tomasLevel 1
asked in Data Ingestion & Infrastructure
Activity filter function on a date

Good day, I need to set a filter at column level to get the release date of the PO.This would be on a TABLE OLAP and the filter does not have to be at component level.In case I do it in a single table, I applyFILTER _CEL_P2P_ACTIVITY_MASTER_DATA ACTIVITY_EN = 'Release Purchase Order '.I imagine that a PU_FUNCTION is used to set the ROUND_DAY("_CEL_P2P_ACTIVITIES". EVENTTIME) that affects only one activity.Thank you very much.

20
D
Level 2
V
Level 2
vijay.seguLevel 2
asked in Data Ingestion & Infrastructure
Unable to apply filter on an aggregate function

Ive a table component with 2 dimension:Dimension 1: Combination of Company code + Vendor ("'BKPF".BUKRS||BKPF.LIFNR)Dimension 2: Count of documents (COUNT(DISTINCT BKPF.BELNR))However Im unable to filter the Document count with count > 1. The system returns the error An aggregate is not a function. Please check that there are no dimension and aggregations used together as function inputs.Can anyone suggest how to achieve the filter?

20
V
Level 2
S
Level 3
stefan.hofma12Level 3
asked in Data Ingestion & Infrastructure
Process Data Model (relationship activity - case table )

Dear all, I have a few different questions about the Process Data Model. I think there have been a few CELONIS adjustments in the past. Even though the relationship between activity and case table is correct (Process will be shown in the process explorer in the correct way) the display 1:N is mirrored (pic #1). In the past it was possible to declare/identify the case table with a . I cant find this option any more in the drop down option (pic #2). In general we need to insert two sources (different systems) in the same process analysis. They are running in parallel and are differentiated by additional information in the case (pic #3). The data is written into the case table. Difference than usual (normal create case table syntax), we use the SQL Vertica insert into syntax like we used in the activity table. We have to match two different systems with different data sources. In my opinion it will work fine, but I am not really sure that we can use it that way. Although I only create

60
D
Level 2
V
Level 2
vijay.seguLevel 2
asked in Data Ingestion & Infrastructure
Error while converting string to date using TO_TIMESTAMP function

Im trying to convert a string field to date using TO_TIMESTAMP function in Celonis but receiving the error Undefined exception occured: ToTimestamp: Input does not fit to date format.The function works well for a constant value.e.g. TO_TIMESTAMP(20210101,YYYYMMDD)but does not work for a table field. e.g. TO_TIMESTAMP(CEL_AR_ACTIVITIES.CHANGED_TO,YYYYMMDD) returns error.Can someone help with the error?Thanks.

30
D
Level 2
T
Level 3
tomoki.tsuneLevel 3
asked in Data Ingestion & Infrastructure
Result of one KPI is affected by other KP

Dear all,I am encountering curious changes of results depending on the number of KPIs and Filters. I put an identifier in EKKO-VERKF for differentiating process patterns. Each pattern goes through maximum four, minimum two process flows. In other words, one CASE_ID contains from two to four activities.Here is the problem: the result changes after you add another KPIS, namely M04 disappears, from M01 to M03 gains/loses a few. I am wondering why one KPI affects the others, or rather if I write this code wrongly.Best regards,[KPIS]CASEWHEN SUBSTRING(EKKO.VERKF, 3, 3) = M01THEN CALC_THROUGHPUT(FIRST_OCCURRENCE[A] TO FIRST_OCCURRENCE[B],REMAP_TIMESTAMPS(ACTIVITIES.EVENTTIME, HOURS)) - 7*24WHEN SUBSTRING(EKKO.VERKF, 3, 3) = M02THEN CALC_THROUGHPUT(FIRST_OCCURRENCE[A] TO FIRST_OCCURRENCE[B],REMAP_TIMESTAMPS(ACTIVITIES.EVENTTIME, HOURS)) - 14*24WHEN SUBSTRING(EKKO.VERKF, 3, 3) = M03THEN CALC_THROUGHPUT(FIRST_OCCURRENCE[A] TO FIRST_OCCURRENCE[B],REMAP_TIMESTAMPS(ACTIVITIES.EVENTTIME, HOURS)) -

00
T
Level 3
T
Level 3
tomoki.tsuneLevel 3
asked in Data Ingestion & Infrastructure
MOVING_SUM using number of process variants

Hi, thank you for dropping by this topic. Im trying to use KPI Format as Start of MOVING_SUM. MOVING_SUM(COUNT_TABLE("_CEL_SD_ACTIVITIES_CASES"), KPI("Number of process variants") *-1,0) This KPI returns me an error: that start parameter should be an integer, which I firmly believe I am giving it integer. Using COUNT DISTINCT instead has not solved this situation btw. This should show the same result as the formula below. MOVING_SUM(COUNT_TABLE("_CEL_SD_ACTIVITIES_CASES"), -2,0) Best

00
T
Level 3
.
Level 2
.14600907Level 2
asked in Data Ingestion & Infrastructure
Convert Seconds to time

Hi Team, I am working on uploading the file data to build eventlog in IBC. In the file, one of the file holds seconds and I would like to convert to time as hh:mm:ss, after that I need to add this time to existing timestamp to get the correct Eventtime in event log. Example : Field 1 Field 2 625 2019-11-20 13:41:25 I need to convert field 1 (625) to Time format hh:mm:ssthen add the time (in step1) to Field2 , this is not only adding, date should automatically change if the time exceeds 24 hrs I tried with the possibility of using ADD_Seconds function but it throws message as the function does not exist. Your suggestions are welcome!add_seconds1313228 10.6 KB

20
C
Level 1
J
Level 3
jonas.schei11Level 3
asked in Data Ingestion & Infrastructure
OLAP table touchless activities

Dear all,I would like to create an OLAP table with the activity as dimension. And I would like the following KPI as a column:If all activities with the name of the activity in the dimension ran automatically for a sales order item, then count 1, otherwise count 0.For example:So if the activity Change Price occurs 3 times for an order item, and all price changes are delivered automatically, then count 1.But if at least 1 price change was manual, count 0. The following code does not work unfortunately. I suspect that is because the dimension is on activity level, and the calculation on item level.2020-12-22_11-09-13761117 28.7 KBSUM(CASEWHENPU_COUNT(VBAP, _CEL_O2C_ACTIVITIES.USER_TYPE, _CEL_O2C_ACTIVITIES.USER_TYPE NOT IN (<%=AutoTypes%>)) = 0ANDPU_COUNT(VBAP, _CEL_O2C_ACTIVITIES.USER_TYPE, _CEL_O2C_ACTIVITIES.USER_TYPE IN (<%=AutoTypes%>)) > 0THEN 1ELSE NULLEND)But is there any way to calculate this?Many thanks in advance!Best regards,Jonas

00
J
Level 3
.
Level 2
.14600952Level 2
asked in Data Ingestion & Infrastructure
Case Frequency vs Activity Frequency

Hello everyone We were checking the description what stands for Case vs Activity Frequency Case Frequency simply counts the number, that this activity is hit by any case, the Activity Frequency counts the distinct cases that pass this activity. The Activity Frequency will thus always be equal or smaller than the Case Frequency. In reality, it looks the other way around activities nr are higher Case vs Activity Frequency.docx (533.0 KB) thank you Tatiana

60
D
Level 2
N
Level 3
nogueira.nogueLevel 3
asked in Data Ingestion & Infrastructure
Can’t make Dynamic Parameters work

Hi, Everyone. Im trying to use a dynamic parameter within a transformation task, but I cant make it work. It doesnt matter how I input the Table Name, I always get the same message when executing the task: Table XPTO to calculate dynamic parameter Test does not exist (yet) - using default. Ive used the table name with and without schema name, with and without quotation marks, even tried with the tables internal ID but nothing seems to work. What am I missing? Thank you for your help. Joaquim

20
N
Level 3
T
Level 2
tomas.kvapiLevel 2
asked in Data Ingestion & Infrastructure
How to display a sub-process in process explorer?

Good morning, I am facing a problem where I need to display subprocesses in process explorer in Celonis. In the process, all cases have a similar path through the same steps. However, at the end of the main process, some cases continue in a subprocess. I found out that there is a function RESPECT PARALLEL PROCESS in Activtivities table. Has someone used this possibility, please? Thank you for advice. Tomas

70
joost.herma
Level 4
J
Level 3
jonas.schei11Level 3
asked in Data Ingestion & Infrastructure
Workdays_between with consideration of hours and minutes

Hi all,I want to calculate the cycle time with the workdays_between function. Is it somehow possible to calculate on a minute level?WORKDAYS_BETWEEN(VBAP.FABKL, PU_FIRST(VBAP, _CEL_O2C_ACTIVITIES.EVENTTIME, _CEL_O2C_ACTIVITIES.ACTIVITY_EN IN (<%=Non_ESOP_Activities%>)),PU_LAST(VBAP, _CEL_O2C_ACTIVITIES.EVENTTIME, _CEL_O2C_ACTIVITIES.ACTIVITY_EN IN (<%=Non_ESOP_Activities%>)))Thanks a lot in advance.Best regards,Jonas

60
J
Level 3
S
Level 2
satrajit.satraLevel 2
asked in Data Ingestion & Infrastructure
Data Load issue in _CEL_P2P_ACTIVITIES for file uploads

Im trying to load some files, the model is correctly loaded with correct foreign key, have loaded both files with the same datatype INTEGER for _CASE_KEY column. While trying to load data, im facing the issue mentioned below: Data model load failed during load to engine: Could not load data model. Load failed. Error Message: Error while joining table PO_DISTRIBUTIONS_ALL_xlsx_Export_Data with table _CEL_P2P_ACTIVITIES_xlsx_Export_Data. Data type of column _CASE_KEY doesnt match data type of column _CASE_KEY. Celonis Support ID: 8e12a1b3-589b-4720-be56-8b90a0fd6e7d Please help.

10
vassilios.falir
Level 2
steven.bernd
Level 3
steven.berndLevel 3
asked in Data Ingestion & Infrastructure
Subtract two easy formulas does not work. why?

Hello everybody,i want to subtract two simple COUNT formulas but it doesnt work.However, if I look at the individual results, then I have a result.Funnyimage996864 63 KBimage273758 4.34 KB image287723 4.39 KBwhat am I doing wrong?ThxSteven

00
D
Level 2
steven.bernd
Level 3
steven.berndLevel 3
asked in Data Ingestion & Infrastructure
FILTER out Cases with condition

Hi everyone,i hope you can help me with a FILTER function.image716234 8.28 KBI need a FILTER function with PU_LAST that deletes my cases (in this case it is 98068) when the last activity of Vorgangsart_gend. in column Wert Neu is like /RWEE/DGF.Can you help?

00
D
Level 2

Badge Winners

  • Value Assessment Expert
    rmandradhas earned the badge Value Assessment Expert
  • Qualified to Create and Deliver Demos
    rmandradhas earned the badge Qualified to Create and Deliver Demos
  • Celonis Certified Data Analyst
    caroline.wende13has earned the badge Celonis Certified Data Analyst
  • Qualified to Prove Celonis Value
    rmandradhas earned the badge Qualified to Prove Celonis Value
  • Qualified to Build Views
    shahbaz.maniyhas earned the badge Qualified to Build Views
VIEW ALL
Community Code of Conduct