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 Sunny. That seems to be what I needed. I think basically 'yes' I need the artificial rows. But this does give me some idea about tweaking the logic.
