topic Re: Data comparison between dates based on fields in App Development

Data comparison between dates based on fields

hitha1512 — Tue, 27 Jul 2021 07:55:46 GMT

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

Re: Data comparison between dates based on fields

mg_gsi_da — Tue, 27 Jul 2021 08:17:48 GMT

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

Re: Data comparison between dates based on fields

ManuelRühl — Tue, 27 Jul 2021 09:07:47 GMT

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

Re: Data comparison between dates based on fields

Vegar — Tue, 27 Jul 2021 12:37:43 GMT

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)

Re: Data comparison between dates based on fields

hitha1512 — Wed, 28 Jul 2021 19:01:58 GMT

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

Re: Data comparison between dates based on fields

hitha1512 — Wed, 28 Jul 2021 19:02:56 GMT

Thank you, that worked. 🙂

Re: Data comparison between dates based on fields

hitha1512 — Wed, 28 Jul 2021 19:27:54 GMT

Something new to learn. Thank you 🙂