Hello,
I’m trying to find out if there’s a more optimised way of achieving the following.
Example data set:
ID | Date | Created | Aggregated |
A | 01-May | 66 | 66 |
A | 02-May | 71 | 137 |
A | 03-May | 24 | 161 |
A | 04-May | 76 | 237 |
A | 05-May | 5 | 242 |
B | 01-May | 82 | 82 |
B | 02-May | 77 | 159 |
B | 04-May | 59 | 218 |
B | 05-May | 91 | 309 |
* Note for ‘B’ – 03 May is missing.
So, if I create a summary by ID, the following results:
| A | B |
02-May | 137 | 159 |
03-May | 161 | 0 |
04-May | 237 | 218 |
However, I expect/want:
| A | B |
02-May | 137 | 159 |
03-May | 161 | 159 |
04-May | 237 | 218 |
i.e. where null, show the previous not-null value.
Alternatively; If I only looked at 03-May in isolation - filtering to this date only, I would see 0 for ‘B’ – when looking at something like ‘Open’ cases, this implies none are open, however 159 are still open from the previous day.
Currently, in the LOAD script, I use a list of all dates and LEFT JOIN on the data to fill the gaps. Then the following;
If(AutoNumberHash128(ID)=AutoNumberHash128(Peek('ID')), Peek(Aggregated)+(IF(LEN([Aggregated]),[Aggregated],0)), (IF(LEN([Aggregated]),[Aggregated],0))) AS [Aggregated]
Essentially; IF the ID matches the Previous ID THEN Previous Aggregated + Additional Aggregated = New Aggregated
But for this I have to add dates to the data set where there is no value, just to fill the aggregation.
To achieve the above I need to artificially create nearly 5 times as many rows as have actual data.
Is this the best way to resolve this issue, or is there a smarter way with an Expression?
Maybe loading all that data isn’t an issue, and having an Expression to achieve the above would impact performance – I just want to be sure I’m applying the best method.
Thanks in advance.
