How do I aggregate total data to a dimension based on a dynamic condition?
I have a table consisting of three columns: Location, Date, Value. Each location appears exactly once.
I want to add a new column Aggr which for each location adds up Value of all other locations whose date is higher than its own date. Here is an example:
JOIN (T) LOAD Location as Location1, Date as Date1, Value as Value1 RESIDENT T;
Z: LOAD Location, Date, ONLY(Value) as Value, ONLY(Aggr) as Aggr, SUM(IF(Date1>Date, Value1)) as NewAggr RESIDENT T WHERE Date1 >= Date GROUP BY Location, Date ;