Flag data based on TimeStamp Activity

[SariPari]

I have data as below in Table 1

Table 1

Project ID	TimeStamp
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

Based on Table 1, I want to create Table 2 - Ex: ABC is only in Jan and deleted in Feb and I want to flag that.

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

Is this doable as there is no data entry for ABC in feb in the table.

@swuehl @Sunny @michel @Jagmeet_singh 

[Digvijay_Singh]

You have not shared any logic to be used to add third column 'flag', is that pure manual entry based on some criteria you wish to use manually like this?

Table1:
Load * inline [
Project ID, TimeStamp
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
]
;

Join(Table1)
Load * inline [
Project ID, TimeStamp, Flag
ABC, 01/01/2023, Inserted
ABC, 02/01/2023, Deleted
DEF, 03/01/2023, Inserted
XYZ, 01/01/2023, Inserted
];

[SariPari]

@Digvijay_Singh  Flag will be a calculated column based on Project ID is inserted or deleted.

Ex: ABC is in Jan but it is deleted in Feb - so it should flag as Deleted for Feb.

[Digvijay_Singh]

I meant how do you get data for these updates, how did you get ABC Feb flag value as deleted? You shared the source table but not the update table, after that we can think of how to manipulate source table to achieve your final output.

[SariPari]

@Digvijay_Singh I only have the source table - updated table is the expected result.Flag column will the calculated column created based on the source table.

[steeefan]

See this post for earlier suggestions on the same topic.