Two where clauses

[#1]

Hi

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

Thankful for help!

[pover]

In the row with sum(Field3), you have to define it alias or sum(Field3) as Field3.

ADD is for partial loads. Maybe you are doing a partial load, but you might be looking to concatenate the tables. It that's the case you should use the function CONCATENATE.

If you have a sum in the script, the other fields in the load have to be included in a group by:

Load Field1,
Field2,
sum(Field3) as Field3
Resident Table
Group by Field1,Field2;

That's all I can see for now. I hope that helps.

Regards.

Regards.

[#3]

Ok, Thanks, I suspected that CONCATENATE might help me but I don't quite know how it works? Could anyone give an example using my table(s) above?

Thanks!