Data Ingestion & Infrastructure

Hi teamThis feels like a simple question but I cant seem to find a way to do it so thought of asking - Im looking to create a OLAP table to support data validation that looks like the below:Table Name__Table CountLFA1__123EKKO__456The idea is that the OLAP will list all tables in the model to easily see the current Table Count for each table.Is there a way I can define a dimension to list all tables in the model that would populate the 1st column of this OLAP table with all table names?ThanksOliver

Hi allI am trying to calculate throughout time from a date column (Scheduled Due Date) to an activity event time (Record Goods Issue). I have tried CALC_THROUGHPUT and SOURCE/TARGET but it looks like the only way these will work is if I define Scheduled Due Date as an activity.Is there a way of coding this without creating a new activity?ThanksOliver

Hi, We have 2 Data Models both with the same customer table. In the first data model I have customer_credit with a 1-1 relation with the customer table. Celonis links them in the data model as 1-n (customer-customer_credit) However in the second data model the same 2 tables are linked with n-1 (customer-customer_credit). How can I ensure the link is 1-n when the tables are 1-1? Best, Joo

Hey all,Is there a way to filter out all cases that start before a certain year?I tried the following:FILTER YEAR(P2P_-_Q2_2018_csv.TIMESTAMP) => 2016But this still gives me cases that start in 2015 in an OLAP table, because there are also activities performed in 2016.

Hi ,I am encountering wrong results with change ratio formula below.SUM(KPI(Priority - Change_Indicator))/ COUNT_TABLE("_CEL_ITSM_CASES")where KPI(Priority - Change_Indicator) isCASE WHEN MATCH_ACTIVITIES(NODE_ANY[ Change Priority Medium to Low, Change Priority Low to Critical, Change Priority Low to High, Change Priority Low to Medium] ) = 1 THEN 1.0 ELSE 0.0 ENDFormatting used is Percentage With DecimalsThis KPI was working fine in our dashboards from long time and started giving weird values since few weeks with same formula.The results of numerator and denominator are ok when executed separately . The only problem is when I combine with both / operator.It also works fine when I change the numerator from MATCH_ACTIVITIES to CASE WHEN _CEL_ITSM_ACTIVITIES.ACTIVITY LIKE %Change% but I dont want to use this because there are tens of formula which need to be modified.I could not identify the exact issue so far to address the customer question-why do same formula give wrong res

Hello everybody,I have a problem, which is definitely easy to solve, but I lack the idea.I have a KPI 1:COUNT (DISTINCT CASE WHEN EBAN. LOEKZ = X THEN EBAN. BANFN ELSE NULL END)I have a KPI 2:COUNT (DISTINCT CASE WHEN MATCH_ACTIVITIES (NODE [Technical Clarification]) = 1 THEN EBAN. BANFN ELSE NULL END)I would like to divide KPI 2 / KPI 1 in a new KPI 3.The new formulaCOUNT (DISTINCT CASE WHEN MATCH_ACTIVITIES (NODE [Technical Clarification]) = 1 THEN EBAN. BANFN ELSE NULL END)/COUNT (DISTINCT CASE WHEN EBAN. LOEKZ = X THEN EBAN. BANFN ELSE NULL END)unfortunately does not work that way.Can anyone help why?Thanks a lot

Hi Community,I need some help on this logic in code editor to give % of completed orders at second time.Sum(All Completed orders with conditional activity = 1 Reason) & Sum of all completed ordersThere are multiple REASONS in Activity column but i need only completed orders with one REASONTrying to calculate the % from a table based on certain activities but limited to the count = 1 .Can you please help with the case statement how to get the % based on above. Here i want to get only count of REASON = 1 for the status & activity = completed for each case.COUNT (DISTINCT CASEWHENtable.ACTIVITY = REASONANDtable.status = COMPLETED ANDtable.ACTIVITY like %COMPLETED%THEN 1 REASONWHENtable.ACTIVITY != REASONANDtable.status = COMPLETED ANDtable.ACTIVITY like %COMPLETED%THEN NO REASONELSE OTHERSEND)Thanks

Hi Team,Can you please help me with the below logic.I am using Case when statement with PU_COUNT_DISTINCT and I need to pass the sum of output to a variable of 1st condition and division with other variable on next when condition in the KPI.Appreciate if anyone helps with the pseudo code.

Hi Team,When I tried to run the below code, the errors says that "Initial parameter must be a table but not column.Note: Parent table and child tables both are same.SUM(CASE WHEN PU_COUNT_DISTINCT(table, table.Order, table.ACTIVITY = REASON) = 1 THEN 1.0 ELSE 0.0 END)Can you please help me to solve this error.Thanks.

Hello, Is there an option to export extracted tables on the back-end as CSV files? Best, Hormoz

Hi Team, In our Data model we are uploading the data through csv using import from file feature, however we need to overwrite the data every week with new upload but need to keep the table name and structure same. I tried uploading new csv file with exact same name but it creates as another new table in the data model. Is there any way we can overwrite the data through csv upload? Thanks, Amogh

Hi, I am possibly missing something obvious here - What is the difference between ACTIVATION_COUNT and INDEX_ACTIVITY_TYPE functions in PQL?

I was wondering if you have some insight on the following issue: (1) We added a new string field to our activity table (2) When it is populated with large values (actual content varies in length from 50 100) we see two things happen: Case explorer no longer loads (we just see a blue spinner)An olap table that tries to display this field reports a broken swap file: read invalid size error (3) When we force the value to have a fixed content of length (10) everything works as expected Are there some fundamental limits set on the activity table to limit its size and make loading (application end) more efficient? Is there best practice on the structure and size of the core activity table? image1116413 7.68 KB

I would like to add a column to case explorer from another table that is not the case nor the activity table. I am not using the cloud version of Celonis and I could not find a way to pull columns from other tables. There is a table that is linked to my case table, from where I want to pull a specific column which has comments. Is there a way to do this?

Hello friends!Will you be so kind to help with one issue:I have standard activity table: CASE_ID, ACTIVITY_NAME, EVENT_TIMEPlease find in attachment a sample of table.I want to make diagram chart (or OLAP Table) to show how many cases are being delivered at a certain month.For example -The activity Start of delivery for case #15 takes place on 05.01.2020 and the **next **activity End of delivery takes place on 10.06.2020. That mean that case #15 is being delivered on January, February, March, April, May, June.The activity Start of delivery for case #16 takes place on 07.01.2020 and the **next ** activity End of delivery takes place on 12.04.2020. That mean that case #16 is being delivered on January, February, March, April.So the diagram chart (or OLAP TABLE) should show followingMonth Case CountJan 2Feb 2Mar 2Apr 2May 1Jun 1Will you please clarify which formulas or functions I have to use.Thank you

Hello,I am trying to export to excel data which contains a saved formula in some columns. I have downloaded Celonis APP for AP: Payment behaviour. I have noticed that when I change from saved formula KPI to formula directly, then there is no issue to export to excel. If I refer to saved formula in column value, then I get error Failed: Code 500.Is there a possiblity to export data to excel which includes saved formulas in column values?Thank you in advance for clarification

Hello everyone,I want to analyze how many vendors were just created for one order.What did work?I created an OLAP table with vendors and a simple case count.What is not working?I am not able to count the number of vendors with one case. So, I would like to have a KPI Number of One-Time-Vendors and it should show for example 150.Can anyone help me with this issue? It should be basically equivalent to an Excel countif.Thanks in advance.Best regards,David

Dear Colleagues,can you please let me know if it is possible to have the distinct string PU function applied in Celonis?In my specific case I need to know what currencies the vendors have used in the past. I can easily do the Count Distinct, which gives me the number of currencies, but I also wish to know what were these currencies. With PU_STRING_AGG I get the full list from all the documents, but I only need a string of distinct values - in my case USD and PEN. Can it be achieved?Of course I could create a separate Olap with currency as dimension, but the point is that this table needs to be used in Action Engine, so that is why I need it in single OLAP.Thank you in advance for any suggestions.image1560826 19.9 KB

HI there,Can anybody help with the component filter to apply in order to focus the Process Explorer to include all pathways including the Change Price node.Many thanksChristian

Dear Colleagues,a simple question regarding function ADD_MONTHS. Is there a way to include in it leap year? I am calculating an expiry date for certain financial documents using formula:ADD_MONTHS ( ROUND_DAY(TO_TIMESTAMP(RBKP.BLDAT,YYYY.MM.DD)),3)So expiry is after 3 months of document date and if it is 29.11.2019, then the result should be 29.02.2020 instead of 28.02.2020. In excel we have function EDATE. Can we achieve something similar in Celonis?

Hello,Im trying to set up Action Engine skills using standard deviations from KPIs to trigger the firing. However, its not the Action Engine part Im having trouble with, its the standard deviation calculation.Essentially Im trying to calculate the standard deviation of client abcs cancellation % from all clients cancellation %.Ive experimented with the STDEV function but because the cancellation % function already has AVG (an aggregate) it doesnt work.Cancellation formula:AVG(CASE WHEN AuthMgmt_Tasks.auth_outcome IN (Cancelled after work,Cancelled prior to work)THEN 1.0 ELSE 0.0 END)Hoping someone has experimented with this before, appreciate any help!

Hey Celonis Community, If I want to show the average TPT between different steps in the Process Explorer the TPT shown should be around 1 to 30 days, instead it is now around Billions and Millions of days (see attachted file). (Same applies for the median TPT and the trimmed Median TPT as well as if I calculate the TPT by myself.) Is anybody else having this problem or can help me out? Thanks a lot! Jonas Process Explorer displays wrong TPT values945807 48.6 KB

Hello, I have loeaded my data model successfully but when i open my analysis there is no data (even the poucentage of cases). This error appears in all components : too many stages in execute_like Do you have any idea what causes this issue please an how can i fix it? PS: there is no filter applicable on the whole analysis and i kept the data model very simple. Thank you in advance

Running a standard transformation, Im encountering an occasional source data error (e.g. an alpha in an INTEGER column). Of course, the script halts immediately. The source environment is highly customized and there will be a few of these. DataException: [Vertica]VJDBC ERROR: Could not convert R03B802 from column table.numeric_field to a float8 This error message does not reveal a row number. Im loading over 20M records, so going back to source to locate and fix each data anomaly and then going through a long reload each time ugh. I cant get to the juicy part of this great tool because of the time spent returning to source data, one element at a time, cleansing and reloading. Im new to this, but it seems like Im missing something simple here. Is there a way to patch data post-extraction?

Hello all, I spent quite some time exploring all possible functions but Im unable to solve this.I need to do a running last based on the table as below. I will then use this to minus off the actual quantity. Ive explored the MOVING_X function, PU_X function and it doesnt seem to give me what I need. Any ideas? Appreciate the inputs!a869462 55.6 KB