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

[Parthiban]

Hi,

Try this way

Table:
load * inline
[
ProjectID TimeStamp
ABC 01/01/2023
ABC 02/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
](delimiter is ' ');

Lastvalue_Table:
load LastValue(ProjectID) as ProjectID_Latest,
LastValue(TimeStamp) as Date
Resident Table
Order by TimeStamp;

Then In Dimension use this,

=if(ProjectID=ProjectID_Latest,'Live','Deleted')

[Rohan]

Dear Sari,

Firstly you will have to figure out the 1st entry of a new Project & the Last entry of the existing project. ie :

Load 

ProjectID,

max(Timestamp) as Last,

min(Timestamp) as first

resident Data group by ProjectID;

Then you will have to left join this into your Data & use the following If :

if(Timestamp=first,'New Addition',

if(Timestamp=last,'Deleted next month', 'Active')) as Flag

I hope this will solve your query. Revert if any issues.

Regards,

Rohan.

[steeefan]

You could also directly flag the relevant records in your code (TS conversion only to get MM/DD/YYYY working on my system):

Table:
LOAD * INLINE [
ProjectID TimeStamp
	ABC 01/01/2023
	ABC 02/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
] (delimiter is ' ');

Data:
NOCONCATENATE LOAD
	ProjectID,
	Date(Date#(TimeStamp, 'MM/DD/YYYY')) AS TimeStamp
RESIDENT
	Table;

LEFT JOIN (Data) LOAD
	ProjectID,
	True() AS isMinTS,
	Date(Min(TimeStamp), 'MM/DD/YYYY') AS TimeStamp
RESIDENT
	Data
GROUP BY
	ProjectID,
    True();

LEFT JOIN (Data) LOAD
	ProjectID,
	True() AS isMaxTS,
	Date(Max(TimeStamp), 'MM/DD/YYYY') AS TimeStamp
RESIDENT
	Data
GROUP BY
	ProjectID,
    True();

DROP TABLE Table;

 This code flags the first and last entry for each value of ProjectID:

With that you can then take it further and create more flags that you need.

[Anil_Babu_Samineni]

@SariPari I will suggest doing all this Audit from database (Ask Admin to get that in your schema) and you can pull. Because DML operation very much flexible in DB side.

[SariPari]

Tried this but this did not work

Data:
load * inline
[
ProjectID TimeStamp
ABC 01/01/2023
ABC 02/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
](delimiter is ' ');

TableTmp:
Load

ProjectID,
TimeStamp as Timestamp

resident Data;
Drop Table Data;

left join TableTmp:
Load

ProjectID,

max(Timestamp) as Last,
min(Timestamp) as First

resident TableTmp group by ProjectID;

Table:
Load

ProjectID,
Timestamp AS TimeStamp,
Last,
First

Resident TableTmp;

LEFT JOIN Table:
lOAD

ProjectID,

if(TimeStamp = First,'New Addition',
if(TimeStamp = Last,'Deleted next month', 'Active')) as Flag

Resident Table;
drop table TableTmp;

exit Script;

[Saravanan_Desingh]

Try something like this,

tab1:
LOAD * INLINE [
    Project ID, TimeStamp
    ABC, 01/01/2023
    ABC, 02/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
];

Left Join(tab1)
LOAD Date(Max(TimeStamp)) As MaxDt
Resident tab1;

Left Join(tab1)
LOAD [Project ID], FirstSortedValue(TimeStamp,TimeStamp) As F1, FirstSortedValue(TimeStamp,-TimeStamp) As F2
Resident tab1
Group By [Project ID];

Left Join(tab1)
LOAD [Project ID], TimeStamp, If(F1=TimeStamp,'Inserted', If(F2=TimeStamp And F2<>MaxDt,'Deleted')) As Flag
Resident tab1;

[SariPari]

Thnak you so much..this almost worked based on the requirement.

Now they are aksing when the record is not in the entry.

Ex: ABC is only in Jan but not in Feb.Can this be flaged as deleted even when there is not entry ?

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

[Rohan]

Hi Sari,

If you want an additional entry for the deleted ones, then simply add this code in your final script;

Noconcatenate

Final_Data:

Load *,Timestamp as TimestampNew

Resident Data;

*This part will handle the scenarios where the Project was active for atleast 2 months*

Concatenate(Final_Data)

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

Resident Data where Flag='Deleted';

*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;

Drop table Data;

Regards,

Rohan.

[SariPari]

Thank you Rohan,,,but I am messing somrthing here...Please see the script I have below

Data:
load * inline
[
ProjectID 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
](delimiter is ' ');

TableTmp:
Load

ProjectID,
TimeStamp as Timestamp

resident Data;
Drop Table Data;

left join TableTmp:
Load

ProjectID,

max(Timestamp) as Last,
min(Timestamp) as First

resident TableTmp group by ProjectID;

Table:
Load

ProjectID,
Timestamp AS TimeStamp,
Last,
First

Resident TableTmp;
DROP TABLE TableTmp;

LEFT JOIN Table:
lOAD

ProjectID,

if(TimeStamp = First,'New Addition',
if(TimeStamp = Last,'Deleted next month', 'Active')) as Flag

Resident Table;

Noconcatenate
Final_Data:
Load
ProjectID,
TimeStamp as TimestampNew,
Last,
First,
Flag

Resident Table;

Concatenate(Final_Data)
Load
ProjectID,
Last,
First,
Flag,
date(addmonths(TimeStamp,1)) as TimestampNew

Resident Table where Flag='Deleted next month';

Concatenate(Final_Data)
Load
ProjectID,
Last,
First,
Flag,
date(addmonths(TimeStamp,1)) as TimestampNew

Resident Table where Flag='New Addition' ;

Drop table Table;