Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
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
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
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)
Thank you 🙂 Haven't tried it yet, will try this solution.
Thank you, that worked. 🙂
Something new to learn. Thank you 🙂