Data Ingestion & Infrastructure

Hi there, this probably has some super simple solution but im kind of stuck at the moment.I basically want to count, how often a certain activity happens per case. My idea was to apply a filter so that i only have rows containing that activity. For example i now have 4 collums with case id 1. Now i just want to create a new collum that shows how often the activity was done in each case by just counting how many rows share the same case id. For example of case id 1, there should be a collum at the end, that shows "4" in every row with the case id 1. Thanks for your help once again and kind regardsAaron

Hi there,i've been doing some tasks for uni and stumbled across a task where i need to return 'AB' if activity A happened before B and 'BA' if B happened before A. We can assume that always exactly one of the cases is true, so no null solutions. I tried the following PQL formula but it doesnt seem to work:  CASE WHEN(PU_Last("case-table", "activity-table"."ACTIVITY", "activity-table"."ACTIVITY" = 'A') < PU_Last("case-table", "activity-table"."ACTIVITY", "activity-table"."ACTIVITY" = 'B'))THEN 'AB'ELSE 'BA'END For some reason the first case never happens. Anyone having any ideas?Thanks for your help and kind regardsAaron

for a given vendor we have an invoice date and I would like to find the date of the last invoice relative to this current date. So for example we have date of invoice for given vendor 2.01.2023 and I would like to find date of last invoice for same vendor, that is for example 1.01.2023.In celonis I would like to have in one column the date of the "current invoice" and in the other column the date of the previous invoice 

 Execution error: Not allowed to access schema "VBAP" After I have extracted the necessary tables from SAP. I am in the transformation phase and I want to see the output of my SQL code after executing the code in Transformation Statement.However, I get the following error:  Execution error: Not allowed to access schema "VBAP"I am an admin in Celonis.

i have got some excel and csv files to work upon im not able to add this data into celonis ems platform and do further operations

Hi Gurus, I am trying to create a PQL KPI for a View that will sum a field called TRX_rev_amount. Its a negative number so I'm using the ABS function. For some reason, it is working great when I have it in charts or as a KPI list at the header level (see attached screenshot), but it does not work when I have it in a table. TBH I have no idea how it is coming up with the amount of $3.14k. It should be the same as above ($524). The code I'm using for the KPI is: SUM( FILTER_TO_NULL(ABS("ar_billing_transactions"."trx_rev_amount"))) If I change the code to a PU_SUM (see below) with a domain field to aggregate it by the trx_number it will work for the table, but then does not work in the header or in charts etc. What should I be doing differently?  PU_sum (  DOMAIN_TABLE ( "ar_billing_transactions"."trx_number" ) ,  ( ABS ("ar_billing_transactions"."trx_rev_amount" ) ))

Dear Experts, I want to use the detected deviations informed by process ai as dimensions in the olap table. ​How can I use it?  Now, I am writing by hand by looking at activities corresponding to deviations in process ai.

 I have two PQL statements i want to factor into a clickable link: currently have this as the Component filter: I have two PQL statements i want to factor into a clickable link: I currently have this count: count(case When "_CEL_ENG_LATEST_EVENT"."ACTIVITY_EN" = 'new change' Then 1 else null end) This is tracking cases where the latest event type matches "new change". currently have this as the Component filter:filter(WORKDAYS_BETWEEN("_CEL_ENG_LATEST_EVENT"."EVENTTIME","_CEL_ENG_LATEST_EVENT"."LAST_UPDATED",WEEKDAY_CALENDAR ( MONDAY 09:00 - 17:00 TUESDAY 09:00 - 17:00 WEDNESDAY 09:00 - 17:00 THURSDAY 09:00 - 17:00 FRIDAY 09:00 - 17:00))) > 3 This is displaying the cases that breach my SLA of 3 days I want to be able to have a clickable link that can carry those filters through.

why does it show Data model loads=0 (in Data processing) even after the data model is loaded and I can see it in the Output

Hello guys,I have created a table with some invoices and i want a filter only on the invoices when the last activity is 'to verify' I have tried many solutions like PU_count_distinct, PU_last,... but i dont see any other solutions ! Thanks for your help !

initial output : C76_XX_XXXX_INVOICE_LIV_XXXX_XXX.001.pdf what i would like :C76_XX_XXXX_INVOICE_LIV_XXXX_XXX_001.pdf Thanks for your help

Hey Celopeers community! I am attempting to create a change activity based on a changing list of values, below is an example of a case where a change activity should be created.  Case ID | Date | Value1 | 2021-8-20 | A1 | 2021-8-20 | B1 | 2021-8-20 | C1 | 2021-8-21 | A1 | 2021-8-21 | B As you can see the list of values changed from A, B, C on 8/20 to A, B 8/21  I looked at the VERTICA function library and found LISTAGG function that would help solve this by concatenating fields into one single row, but, unfortunately, it is not enabled in Event Collection.  Are there any workarounds you can suggest? 

Dear Experts, I am attaching snapshots for my query , Basically I have to create Lineplot with X axis being month at which Non compliance occured and Y axis being Total Non Complianced cases. But even though i tried all permutations and combinations , Line plot is unable to appear and all the time green dots are appearing. I request your assistance   

Hi,  I have stumbled upon an warning message that states "The join between table "_CEL_P2P_CASES_6" and table "_CEL_P2P_ACTIVITIES_6" did not find any matching rows."  Prior to this warning message, I have assigned the activities and case table respectively as well as assigned their foreign key, "Case_ID".  Is there a way to fix this error message?

How to schedule a syncronization of data? I have some tables on another connection, from SAP Ariba (Purchase Requisition e Sourcing)We use some of these tables on data model on P2PSo I need to run detal load every day on all jobs.How can I syncronize automatically data from another connections, once when scheduling I only found the option to do it manually?Thanks

I have requirement , user wishes to upload some excel files downloaded from external tool till integration is well established. What can we do so that User can place the file in one fixed folder and Celonis can upload on regular interval like monthly. Can you please guide me here ?

Hi All, I am facing an issue while uploading data to Celonis. My transformations are working fine but it fails at the data model load stage with the following error: "The Data Model Load experienced an issue with the Data Pipeline Orchestrator." Has anyone faced this issue before or knows of a solution to fix this. Thanks,Dhruv

Hi All,  I have an interesting requirement, where i need to find the data type of the column (table field) in PQL.  Ex : Implementation is done in Views and knowledge model:  Case_Key = 400100200300,400A00200400Variable ${CK} = Case_Key  Requirement ==> Case when ${CK}= String then ${CK} else to_String(${CK}) END Any insights on this would be much appreciated. TIA!

Hallo,I would like to query data stored within the Transformation Center.How are the table names i have to query? Is there a list of those somewhere available? KRAlex

Hey there! I'm having trouble selecting cases from a table, that match a calculated condition.  The data I'm trying to analyse is about quality-checks. A quality-check consists of multiple rows in which the result (minor fault/major fault/no fault/not relevant) of each checkpoint is delivered. My goal is to calculate the quality of each quality-check and display those quality-checks which didn't reach a certain percantage of quality.  What I already did is: Calculate the quality of each check in %1- (SUM(STR_TO_INT(REMAP_VALUES("CASE_QS"."RESULT", ['no fault', '0'], ['minor fault', '5'], ['major fault', '10'], ['not relevant', '0']))) / (COUNT("CASE_QS"."CHECKPOINT")*10))Create condition to evaluate whether the desired quality of 99,9% isn't reachedCASE WHEN (1- (SUM(STR_TO_INT(REMAP_VALUES("CASE_QS"."RESULT", ['1', '0'], ['2&#3

Hi Experts , is it possible to receive KPIs from other analytics tool like Qliksense and Power BI. using APIs ?

Hi everyone,  I am trying to modify the duplicate checker app. Precisely, I have a column in BSEG (called MANUAL_CN, of Yes/No type) which I would like to use as a filter which exclude groups of potential duplicate invoices if there is at least one invoice for which BSEG.MANUAL_CN='Yes'. Unfortunately, I am in the situation where no common table could be found so I was wondering if there is a workaround of this issue.  Here is the data model: I have tried to use BIND function, but not successfully... so any suggestions would be highly appreciated!  Thanks in advance!

I am trying to upload a file provided by Celonis for demo purpose into my Celonis environment. The file already contains the _CEL_CHANGE_DATE column in it. While uploading the file, I'm getting an error "ERROR: Duplicate column "_CELONIS_CHANGE_DATE" in create table statement.". Is there a way I can disable this column generation during upload or any other workaround. Appreciate your help.

 I used some PQL functions like TIMELINE_COLUMN/TABLE to build a common time axis to try to plot the invoices that were open at any given date but I have not been able to make it work.

Dear Experts , I have peculiar requirement which i might be making more complicated. Here is the requirement. Lets say X is the document that gets posted every day and Some documents are with "Y" status and some are not . Requirement is to calculate Percentage of document posted each "Month" with Y status . So KPI should look like  Jan 2023 - percentage of x document posted with y status in Jan are 10% Feb 2023 - percentage of x document posted with Y status in Feb are 23%  My challenge is how to ensure in PQL query that in this formula given below i am only using Total document in "Given Month"  Formula = X document with y status / total docs posted in "Given Month" . Any guidance / keyword / help is requested.