Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table "Daily_User_Level_Details" as follows :
Date | User | Attempts |
2023-01-23 | AB | 2 |
2023-01-23 | CD | 1 |
2023-01-23 | EF | 3 |
2023-01-24 | AB | 1 |
2023-01-24 | EF | 1 |
2023-01-24 | MN | 2 |
Everyday I want to count the number of users and their total attempts "till date" - per day in buckets of "1 attempt", "2 attempts", "3 or more attempts". Hence, for the above scenario the resultant table "Attempt_Aggregation" would be like (only consider date, bucket, UserCount column. the 4th column reason is just for explanation in this post) :
Date | Bucket | UserCount | Reason |
2023-01-23 | 1 attempt | 1 | Only 1 user i.e. CD has 1 attempt on 1/23 |
2023-01-23 | 2 attempts | 1 | Only 1 user i.e. AB has 2 attempts on 1/23 |
2023-01-23 | 3 or more attempts | 1 | Only 1 user i.e. EF has 3 attempts on 1/23 |
2023-01-24 | 1 attempt | 0 | AB and EF are not new users. They had 2 and 3 attempts respectively yesterday |
2023-01-24 | 2 attempts | 1 | MN has 2 attempts on 1/24 and 0 for all dates before 1/24 because MN new user |
2023-01-24 | 3 or more attempts | 2 | AB's 2 attempts on 1/23 + 1 attempt on 1/24 = 3 in total "till date" and EF's 3+1 = 4 attempts till date. Therefore there are 2 users who fall in "3 or more" bucket i.e. AB and EF |
I can do this using aggr() function at the chart level. However, we have a date range of 2 years and more than 40,000 users per day. This will make it very slow. Hence I want to do this at script level and store each day's calculation in a QVD on an incremental level, so that we dont have to compute the historical numbers every day. But I am struggling with developing the logic this way. Hence need some guideline / direction. Please help.
Thank you.
Hi
Try like below
Temp:
LOAD * INLINE [
Date,User,Attempts
2023-01-23,AB,2
2023-01-23,CD,1
2023-01-23,EF,3
2023-01-24,AB,1
2023-01-24,EF,1
2023-01-24,MN,2
];
Load *, if(TotalAttempts >=3, '3+', TotalAttempts) as AttemptBucket;
Load *, if(User = Previous(User), 1, 0) as OldUser, if(User = Previous(User), Peek(Attempts)+Attempts, Attempts) as TotalAttempts Resident Temp Order by User, Date;
DROP Table Temp;
Front End:
Dim: Date, AttemptBucket
Exp: Count(User) or Count(Distinct User)
Depends on ur requirement.
Hi
Try like below
Temp:
LOAD * INLINE [
Date,User,Attempts
2023-01-23,AB,2
2023-01-23,CD,1
2023-01-23,EF,3
2023-01-24,AB,1
2023-01-24,EF,1
2023-01-24,MN,2
];
Load *, if(TotalAttempts >=3, '3+', TotalAttempts) as AttemptBucket;
Load *, if(User = Previous(User), 1, 0) as OldUser, if(User = Previous(User), Peek(Attempts)+Attempts, Attempts) as TotalAttempts Resident Temp Order by User, Date;
DROP Table Temp;
Front End:
Dim: Date, AttemptBucket
Exp: Count(User) or Count(Distinct User)
Depends on ur requirement.