Celonis dynamic SQL or loops

Hi @zahid.yasar,

Vertica is an SQL based database. Unfortunately, it offers only limited loop capabilities. I would suggest, that you describe in more detail what you want to achieve. Probably someone had this issue before and we can jointly solve it!

Best,

Justin

Hi @justin.stegm ,

It was last years question actually. 😀 However, my question is still valid because we have really complex transformations with one of our customers even more complex than last year. We have some recursive tables and I need to let's say go like 30 - 40 times iteratively and I need to write each iteration to a table. Each iteration gives me the document after.

It's like this,

Lead Doc. -> Sales Activity Doc. -> Opportunity Doc. -> Sales Activity Doc. -> Quotation Doc.-> Sales Order Doc

The data has recorded like below:

This is just a sample. This process is more complex and we have to go thourgh the table so many times manually. We are actually looking for a better and easier way to get this data. Do you have any idea how we can do it in Celonis?

Thanks.

Zahid.

Hi @zahid.yasar,

have you already tried to use the ML Workbench to loop through the tables?

Best,

Justin

Yes we tried it but the problem is we cannot read the tables in vertica schema. We can only access the one that we include in data model and we don't want those tables to load with the data model every time. Is there a way to access vertica side?

@zahid.yasar: Unfortunately you can not access them without adding them to the data model.

I think Celonis should improve its ETL capabilities because when we have complex logic and ETLs, we are having difficulties.

@zahid.yasar, have you tried querying Vertica's system table with a list of all tables?

select * from all_tables

where schema_name not like ('v_%');

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/ALL_TABLES.htm

I encourage you to try also PyCelonis, which allows to retrieve a list of tables in a data pool: https://celonis.github.io/pycelonis/1.6.1/reference/celonis_api/event_collection/data_pool/#celonis_api.event_collection.data_pool.Pool.tables

Hi @piotr.deren12 ,

I didn't really understand your first solution. I mean I can read them, but it doesn't do any good for the logic we are tryin to implement.

About your second suggestion, we already tried that. If you read my comments before, you can see why it is not really applicable for us.

If you have another solution that we can try, that would be great.

Regards,

Zahid.