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.

  • 806 Topics
  • 2,121 Replies
806 Topics
S
Level 2
sören.krupkLevel 2
asked in Data Ingestion & Infrastructure
How to "round_minute()" in data jobs ?Neither round_minute() nor date_trunc() are working for me

For joining tables by timestamp I need to round by the minute. I found date_trunc listed as supported function but can't get it to work. update TABLEset MATCH_TIME_START_MINUTES = date_trunc('minute', TIME_START); gets me this: Execution error: ERROR: Function date_trunc(unknown, varchar) does not exist, or permission is denied for date_trunc(unknown, varchar) TABLE.TIME_START is converted with to_timestamp() before and then looks somewhat like '2022-02-20 01:04:32'. How can I "round_minute" in data jobs?

00
S
Level 2
jan.tran
Level 3
jan.tranLevel 3
asked in Data Ingestion & Infrastructure
I need to sum up a duration from a table with m entries and refer the KPI to a dimension from another table with n entries (for more information see details or picture).

I have 3 tables:Fact Table A with n entries per case and a time dimension (round_hour)Case Table which containts the case keysAnother Fact Table B with m entries per case and a time dimension (round_hour)The data model is n : 1 : m. Unfortunately a generel snowflake model is not possible. Now I need to sum up the duration from Fact Table B aggregated by the time dimension from eventtime2 (round_hour) and display the sum in a chart. The time dimension for the chart is represented by the time dimension eventtime 1 from Fact Table A (round_hour). I tried bind, domain_table in combination with PU-Functions but nothing worked.  Is there a any way map those time dimensions from different fact tables with a n : 1 : m relation? 

100
jan.tran
Level 3
R
Level 4
raman.silot14Level 4
asked in Data Ingestion & Infrastructure
Column name is reserved error in extraction

Hi, I am receiving "Column name is reserved" for a particular column in table that is causing the complete extraction of that table to fail Is there any way to change the name of column while extracting ?

10
G
Level 8
I
Level 2
iyinoluwa.awonu11Level 2
posted in Data Ingestion & Infrastructure
I want to display only the sum of all (claim cap) for Parcel status that were said to be lost.

I want to display only the sum of all (claim cap) for Parcel status that were said to be lost. I will like to use a 'Number KPI'. Please can someone explain to me how I can go about it?  

02
I
Level 2
R
Level 1
ronaldo.fulgeLevel 1
posted in Data Ingestion & Infrastructure
Hello everyone, I have a question about 2 PQL formulas.

Hello everyone, I have a question about 2 PQL formulas.😅  In this case, I need a conditional that checks if it is the last record of the ID, if so, make a formula that takes the timestamp of this record and does the DAYS_BETWEEN between this date and today's date. If the timestamp of that record is not the last one, use the formula below:  AVG( DAYS_BETWEEN ("table_activities"."TIMESTAMPLE", LAG ("table_activities"."TIMESTAMPLE", ORDER BY ("table_activities".."TIMESTAMPLE" DESC) , PARTITION BY ("table_activities",."PROCESS_CELONIS", "table_activities"."ID") )))  I think it would be something like this: CASE WHEN PU_LAST ("tabela_atividades"."TIMESTAMPLE") = "tabela_atividades"."TIMESTAMPLE" --this for each id THEN  DAYS_BETWEEN (AVG( DAYS_BETWEEN ("table_activities"."TIMESTAMPLE", TODAY)  ELSE  AVG( DAYS_BETWEEN

50
R
Level 1
antonio.figue
Level 2
antonio.figueLevel 2
posted in Data Ingestion & Infrastructure
What is the difference between Signal and Object Links?

What is the difference between Signal and Object Links? Hi, i am investigating how to best create network graphs for a BOM and I came across the Signal Link and Object Link. I already have a basic BOM using Signal Link but as i want to use the LINK_PATH operator i now need to switch the BOM to Object Links. I'm having trouble in understanding the difference between the two and what are the pros/cons of one or the other.  Does anyone have experience using the Signals, and can expand on the differences? Thanks in advance for your insights.

212
antonio.figue
Level 2
niclas.schmi11
Level 3
niclas.schmi11Level 3
asked in Data Ingestion & Infrastructure
Filter for two entries from the same column / 'AND' instead of 'OR'

I wanted to use the following filter statement which unfortunately doesn't work:FILTER "Table"."type" = (<%=type1%>) AND "Table"."type" = (<%=type2%>); Strangely enough, the filter does work if I replace the 'AND' with 'OR' like the following: FILTER "Table"."type" = (<%=type1%>) OR "Table"."type" = (<%=type2%>);. However, this would have the same result as an 'IN' statement like the following:FILTER "Table"."type" IN (<%=type1%>, <%=type2%>); But I need an 'AND' or an 'IN' where all selected 'types' are true and not only one from the 'IN' list. Can someone please help me with that issue?

80
L
Level 3
D
Level 3
dinesh.kumar35Level 3
asked in Data Ingestion & Infrastructure
How can we define the important FIELDS from tables that are needed for for P2P, AP, O2C and AR (all possible major use cases

Hi Experts,Our IT is asking for predefining all the data fields for each table for them to create views to be used for process mining by Celonis. We need the same for P2P, AP, O2C and AR (all possible major use cases for now) but not able to get anywhere. IT has not approved to make tables available fully due to security reasons and space constraints. please guide

20
D
Level 3
D
Level 3
dinesh.kumar35Level 3
asked in Data Ingestion & Infrastructure
Best way for Delta Load from HANA Database to avoid any historical data added at a later date on HANA Database

Hello,Could you please advise how to avoid missing on data which was loaded to HANA Database after the extraction was done? In my case, Data is being loaded and consolidated onto HANA (no direct connection to SAP but through HANA database) and a delta load is run for Celonis as per timestamps. Now if some new business segment is added, any data prior to last "delta load" is not getting ingested to Celonis as it belongs to older dates. I know a full data load for a period can do the trick but that's not the efficient way. Any suggestions please?

40
C
Level 2
joos.buijs
Level 6
joos.buijsLevel 6
asked in Data Ingestion & Infrastructure
How to create a date range

We are plotting numbers of cases finished per date. However, when there are no casesfinished on a particular date, the date is not present in the chart, and hence were losing information.A solution could be to create a data range, say from all days between 1-1-2019 and TODAY(), making sure that every day is represented in the chart.We have similar issues when plotting on hour of the day (0-24), day of the week (0-6), etc.

70
W
Level 2
A
Level 2
aaron.peschLevel 2
asked in Data Ingestion & Infrastructure
Counting rows that share the same case id

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

10
G
Level 8
A
Level 2
aaron.peschLevel 2
asked in Data Ingestion & Infrastructure
PQL formula to filter activity sequences by first occurrence

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

90
A
Level 2
J
Level 3
julia.terec11Level 3
asked in Data Ingestion & Infrastructure
I would like to create a column where the date of the last invoice for a given vendor is shown against another invoice date.

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 

00
J
Level 3
D
Level 3
dilyar.iskan11Level 3
posted in Data Ingestion & Infrastructure
 Execution error: Not allowed to access schema 'VBAP'

 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.

301
D
Level 3
A
Level 2
akhil.tomLevel 2
asked in Data Ingestion & Infrastructure
I have tried to upload some csv and xlsx files , i am not getting any import icon in celonis tool

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

00
R
Level 4
T
Level 2
timothy.brown12Level 2
asked in Data Ingestion & Infrastructure
SUM vs PU Sum confusion

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

210
T
Level 2
S
Level 3
sanghyeok.kwakLevel 3
asked in Data Ingestion & Infrastructure
Can detected deviations in process ai be used as pql?

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.

10
S
Level 3
D
Level 2
daniel.burroLevel 2
asked in Data Ingestion & Infrastructure
How can add a filtered view into a 'button' / 'link' ?

 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.

90
R
Celonaut
A
Level 1
aishwarya.rajLevel 1
asked in Data Ingestion & Infrastructure
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

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

00
M
Level 1
A
Level 3
antoine.leducLevel 3
asked in Data Ingestion & Infrastructure
How to filter only when the last activity is 'XXXX'

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 !

50
G
Level 8
A
Level 3
antoine.leducLevel 3
asked in Data Ingestion & Infrastructure
Hello guys,I have a varchar column in my database and i would like to replace the 7th character (starting by the end) from a . to a _ I try to use the function LEN but its not working... error message :ERROR: Function LEN(varchar) does not exist

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

40
G
Level 8
A
Anonymous
asked in Data Ingestion & Infrastructure
VERTICA LISTAGG function

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? 

270
janpeter.van.d
Level 8
.
Level 4
.146192833Level 4
asked in Data Ingestion & Infrastructure
Unable to add Lineplot

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   

00
I
Level 1
S
Level 1
sheryl.ngLevel 1
posted in Data Ingestion & Infrastructure
Hi,

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?

21
abhishek.chatu14
Level 7
S
Level 3
sandra.gerbaLevel 3
posted in Data Ingestion & Infrastructure
How to schedule a syncronization of data?

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

11
abhishek.chatu14
Level 7

Badge Winners

  • Qualified to Build Views
    adrian.michlhas earned the badge Qualified to Build Views
  • Qualified to Prove Celonis Value
    anuhya.yedot11has earned the badge Qualified to Prove Celonis Value
  • Qualified to Identify and Drive Opportunities
    anuhya.yedot11has earned the badge Qualified to Identify and Drive Opportunities
  • Qualified to Deliver Business Value with Celonis
    suresh.jassahas earned the badge Qualified to Deliver Business Value with Celonis
  • Celonis Technical Expert
    anuhya.yedot11has earned the badge Celonis Technical Expert
VIEW ALL
Community Code of Conduct