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:
How do I do this in Qlik?
T:LOAD * INLINE [Location, Date, Value, AggrUSA, 01.01.2022, 1, 10Germany, 03.03.2022, 2, 0Japan, 01.01.2021, 4, 11China, 02.02.2022, 8, 2];
JOIN (T)LOAD Location as Location1, Date as Date1, Value as Value1RESIDENT T;
Z:LOAD Location, Date, ONLY(Value) as Value, ONLY(Aggr) as Aggr,SUM(IF(Date1>Date, Value1)) as NewAggrRESIDENT TWHERE Date1 >= DateGROUP BY Location, Date;
DROP TABLE T;