I'm wondering what the best/Fastest way to create a table with "two" where-clauses would be.
This is how I have thought doing it
RawData: LOAD Field1, Field2, Field3, Field4, Date from someqvdfile;
Dates: load Date resident RawData;
Let a = NoOfRows('Dates') FOR I=0 to a Let DateNow=fieldvalue('Date',I)
Table1: LOAD Field1, Field2, '0' as Field3, sum(Field4), $(DateNow) as TheDate resident RawData where Date<=$(DateNow);
ADD LOAD Field1, Field2, sum(Field3), '0' as Field4 $(DateNow) as TheDate resident RawData where Date=$(DateNow);
... and then clear all entries where both Field3=0 and Field4=0 etc...
See what I'm trying to do here? In Table1 I want Field4 to be summed for all dates up to $(DateNow) and this works (verified) and then to this table I want to add the sum of Field3 for ONLY the date on $(DateNow). My solution seems correct in my eyes but doesnt seem to work. What am I doing wrong? Field3 becomes 0 on all entries