Flag Records based on activity.

[SariPari]

Project ID	TimeStamp	Flag
ABC	01/01/2023
DEF	03/01/2023
DEF	04/01/2023
XYZ	01/01/2023
XYZ	02/01/2023
XYZ	03/01/2023
XYZ	04/01/2023

How to Flag Records that are deleted and Inserted ? In the above ex: ABC is deleted from Feb and DEF is added in March.How can I identify that ? XYZ has remained the same.

@swuehl @Michael_Tarallo @petter

[SariPari]

@Rohan - any thoughts on this ?

[Rohan]

Hi @SariPari ,

in the 3rd part you have not added the Min=max date part.

*If project was active only for a single month*

Concatenate(Final_Data)

Load *, date(addmonths(Timestamp,1)) as TimestampNew

Resident Data where Flag='Inserted' and MinDate=MaxDate;

Just try to change that part & check.

Regards,

Rohan.

[SariPari]

@Rohan  - I tried that but it did not work.For both Jan and Feb it flags as inserted record.

[Rohan · Accepted solution]

Hi @SariPari ,

There where 2 mistakes in the script. Firstly, the left join was not correctly done, you had to consider ProjectID & Timestamp to get the flag correctly mapped in the 1st phase. Secondly, there were slight adjustments to the where statements which i did at my end & now it is showing properly, refer attached image & qvf :

You can a final resident & change that "Deleted next month" to "Active" using an if statement, if you require. Kindly check & let me know if any issues.

Regards,

Rohan.

[SariPari]

@Rohan  - Perrrrrrfect - works like a charm!