Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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:
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
Thanks, works like lightning now. 🙂
I tried that first but must have mistyped something then.