Count number of specific values last year?

[OysteinT]

Hi all.

I have a datamodel which isn't too complex:
Examples:

ID	User	Date	Reason	Hours
TEST1_01.01.2021	TEST1	01.01.2021	Reason1	7
TEST2_01.05.2020	TEST2	01.05.2020	Reason1	8
TEST2_01.03.2021	TEST2	01.03.2021	Reason1	8
TEST2_01.04.2021	TEST2	01.04.2021	Reason2	8
TEST2_02.05.2021	TEST2	02.05.2021	Reason1	8

What I want to do, either with a load script, or visualization, add a new column which should do this:
Count all instances of the same user + reason within the last year from the date in that record (including that record).
For example: User TEST2 has 2 instances of Reason1 on 01.03.2021, and also 2 on 02.05.2021 as the earliest one (01.05.2020) is older than within 1 year. 

Data would be loaded once a day. 

[pabloviera · Accepted solution]

Hello, first thing you need is to create a new key for User+Reason as that is the grouping you need to check to count the number of occurrences.

So do add to your table the new Key when loading it: 

...
User&'_'&Reason as ID2,
...

Then you join all the ID2 +Date with your table:

join(MainTable)
Temp:
Load 
ID2, Date as Date2
Resident MainTable;

Now you just need to add the times Date2 validates the condition (It has to be <= than the Date and within 1 year of it):

FinalTable:
Load
ID,User,Date,Reason,Hours,
sum(if(Date>=Date2 and addyears(Date2,1)>=Date,1,0)) as Occurrences
Resident MainTable group by ID,User,Date,Reason,Hours;
drop table MainTable;

Cheers

[OysteinT]

Hi

Thanks - that seems to work when I create it from scratch - wonder why it doesn't work when I apply the same logic to my app...

Can you spot an error here?
(The first part is to make an occurance of getting data in the From-To format into seperate transactions for each date in the range)

Leave_app_dates:
LOAD date(fromDate+IterNo() -1) & '_' & badgeNo as BadgeDate,
	id,
	badgeNo,
    badgeNo&'_'&leaveReason as LeaveIDKey,
	leaveReason,
    restId,
    sickLevel,
    extCode,
	date(fromDate+IterNo() -1) as Date1
    RESIDENT Leave_Apps
    WHILE IterNo() <=toDate - fromDate;
    
    drop table Leave_Apps;

join(Leave_app_dates)
Temp2:
Load
	LeaveIDKey,
    Date1 as Date2
 RESIDENT Leave_app_dates;
 
 FinalData:
 Load 
 	BadgeDate,
    Date1,
    LeaveIDKey,
    badgeNo,
    leaveReason,
//     dayCount,
//     ScheduleHours,
    sum(IF(Date1>=Date2 and AddYears(Date2,1)>=Date1,1,0)) as Occurences
    Resident Leave_app_dates;
    drop table Leave_app_dates;
 

I get errors on the FinalData: part - "Invalid expression".  Get the same error if I comment out the "Sum" line as well. 

(There is an load block in a table Leave_Apps in the top I haven't included since the script runs past it fine.)

EDIT: Attached the app and sourcefile:

[pabloviera]

You need to group by the other fields for the sum to work

...
Resident Leave_app_dates group by BadgeDate,Date1,LeaveIDKey,badgeNo,leaveReason;

Regards

[OysteinT]

Thanks, works like lightning now. 🙂 

I tried that first but must have mistyped something then.