topic Re: Count number of specific values last year? in App Development

Count number of specific values last year?

OysteinT — Fri, 11 Jun 2021 06:44:48 GMT

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.

Re: Count number of specific values last year?

pabloviera — Fri, 11 Jun 2021 10:02:50 GMT

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

Re: Count number of specific values last year?

OysteinT — Fri, 11 Jun 2021 13:29:13 GMT

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:

Re: Count number of specific values last year?

pabloviera — Fri, 11 Jun 2021 13:41:04 GMT

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

Re: Count number of specific values last year?

OysteinT — Fri, 11 Jun 2021 13:43:07 GMT

Thanks, works like lightning now. 🙂

I tried that first but must have mistyped something then.