Looping in vertica SQL in celonis transformations.

Can you provide an example of what the comparison would look like? You may not need to loop. I’ve done something similar when I have to compare based on a specific date and created a temporary “calendar” table instead.

Hi ​@matt.witty13 ,

Sorry for the late reply!

Let’s say we have extracted an SAP table like BSEG/BKPF and is having a data from 1-1-2023 to 1-1-2025. Let’s say now I have two tables current and past. In current table I will be storing data which has a clearing date from 1-7-2024 to 31-7-2024 (that is 1 month data) and in past table I will be storing data which has a clearing date from 1-7-2023 to 31-7-2024 (That is before 13 months data). After this I will perform some joins on this tables which we can ignore for now. Like this now I want to do the looping for all months. For eg: next I will take data of August 2024 as current table data and before 13 months data including August as past table data, like this I want the separation to happen for all the months in the extracted table. 
NB: I cannot do the hardcoding for the dates here.

Thanks,

Akshay

So you want to be able to combine current data and past data. For what purpose? What you’re asking for is possible but I’m not entirely sure it is necessary

Hi ​@matt.witty13 ,

It’s for performing inner join between the two tables, and so I can find same data and perform further logics.
 

Thanks,

Akshay

I guess I’m confused by what you’re trying to loop. What are you hoping the output to be? 

I ask because BSEG and BKPF do not have join criteria on clearing dates so you would wind up with duplicates if you’re trying to join/match on clearing dates.

Seems like it’s just a straightforward join unless I’m grossly misunderstanding.

Hi ​@matt.witty13 ,

The purpose of this logic is not process mining, we are having an replication of an web based application into celonis, where we are dealing with the development of some reports and one among them is duplicate payments, so when performing joins like this we are trying to find common records on both the sides on the basis of 4 attributes, for eg: vendorname, vendorcode, referencedocumentnumber and amount, so when joining a record if it is present in both the sides it is then inserted into another table  and this has to be done for every month in the  source table. And we are not joining on the basis of clearing date, I am mentioning that the tables limit would be clearing date i.e: The current table will be having data of 1 month on the basis of clearing date and the past table will be having data of 13 months on the basis of clearing date.

Thanks,
Akshay

Seems like a WHERE EXISTS then. Bring records that exist in both Table A and Table B and then insert them into a third table. 

I may not be fully understand though. Can you provide a screenshot or an example of what you would want the end product to look like? I’m not sure I’m understanding because you can’t really loop in SQL but you can fake it.