Data comparison between dates based on fields

[hitha1512]

Hi Team,

I have the below requirement and need your help. 

I have data coming in everyday from database. Need to compare today's data with yesterdays data and get the count of existing agreements by comparing status as per below. 

>  The count of agreements that exists in data where field “Status” has changed to “Active” (from another status) day on day 

> The count of agreements that exists in data where field “Status” has changed to “Inactive” (from another status) day on day 

> The count of agreements that exists in data where field “Status” has changed to “Monitor” (from another status) day on day 

example :

File 1 on 20th

Date	Agreement	Status
20/07/2021	TDC	Active
20/07/2021	SNCS	Inactive
20/07/2021	ABC	Active
20/07/2021	Test	Inactive
20/07/2021	XYZ	Active

File 2 on 21st

Date	Agreement	Status
21/07/2021	TDC	Inactive
21/07/2021	SNCS	Active
21/07/2021	ABC	Active
21/07/2021	Test	Monitor
21/07/2021	XYZ	Active
21/07/2021	MNO	Active

OutPut for 21st :

No. of Active : 1

No. of Inactive : 1

No. of Monitor : 1

[mg_gsi_da]

If I expect the Agreement to be a unique value for each day I propose the following:

You may do a Left Join of today data to yesterday data using the Agreement as key. Rename today Status as StatusToday before. Compare the columns Status and StatusToday using a If clause which sets the value of StatusToday after the comparison if there was a change into a new column (ie. StatusChange). If there was no yesterday value it should return something different into the new column like "New" for example.

Afterwards you can count the values in StatusChange

[mruehl · Accepted solution]

Hi Hitha,

I'd try it like this:

tmp:
LOAD * INLINE [
Date, Agreement, Status
20/07/2021, TDC, Active
20/07/2021, SNCS, Inactive
20/07/2021, ABC, Active
20/07/2021, Test , Inactive
20/07/2021, XYZ, Active
21/07/2021, TDC, Inactive
21/07/2021, SNCS, Active
21/07/2021, ABC, Active
21/07/2021, Test , Monitor
21/07/2021, XYZ, Active
21/07/2021, MNO, Active
];

output:
Load
Date,
Agreement,
Status,
if(previous(Agreement)=Agreement,previous(Status),NULL()) as prevStatus,
if(Status=if(previous(Agreement)=Agreement,previous(Status),Status),0,1) as prevStatusCounter


resident tmp order by Agreement, Date;

drop table tmp;

You have to adjust the expression based on your data model and your date selections!
I just used sum(prevStatusCounter)

Best regards

Manuel

[Vegar · Accepted solution]

Assuming your Date field is recognized as a date, then you could do this without any manipulation to your load script by using set analysis. . 

Load the data
Datatransactions:
LOAD * INLINE [
Date, Agreement, Status
20/07/2021, TDC, Active
20/07/2021, SNCS, Inactive
20/07/2021, ABC, Active
20/07/2021, Test , Inactive
20/07/2021, XYZ, Active
21/07/2021, TDC, Inactive
21/07/2021, SNCS, Active
21/07/2021, ABC, Active
21/07/2021, Test , Monitor
21/07/2021, XYZ, Active
21/07/2021, MNO, Active
];

Create  measures/KPIs with these three expressions.

No of inactive:
count({<
   Date={'$(=maxstring(Date))'},
   Status={'Active'},
   Agreement = P({<Date={'$(=dayname(maxstring(Date),-1))'},Status-={'Active'}>})
>} Agreement)

No of inactive:
count({<
   Date={'$(=maxstring(Date))'},
   Status={'Inactive'},
   Agreement = P({<Date={'$(=dayname(maxstring(Date),-1))'},Status-={'Inactive'}>})
>} Agreement)

No of monitor:
count({<
   Date={'$(=maxstring(Date))'},
   Status={'Monitor'},
   Agreement = P({<Date={'$(=dayname(maxstring(Date),-1))'},Status-={'Monitor'}>})
>} Agreement)

[hitha1512]

Thank you 🙂 Haven't tried it yet, will try this solution. 

[hitha1512]

Thank you, that worked. 🙂

[hitha1512]

Something new to learn. Thank you 🙂