Is that Field1='X' and Sum(RTYPE) by ID>5 or Field1='X' or Sum(RTYPE) by ID>5.
I would try to build this up by identifying rows so I you can debug, before tidying up.
So for the Sum(RTYPE) I would create a table;
Group By ID;
Then take a look at that, see if it makes sense, if it does left join back to the original table (either directly or by a new load then dropping) and then it depends on your answer to my question above ... I think.
That does work, but it shows me all ID's where all RTYPES of any field1 are >5.
I just want the sum of RTYPES where field = 'x' > 5.
(DATEITYP = 'R' and field1= 'x',1,0) as RTYPE
and that seems to be working, but the sum is totally weird.
When i look at the table i get i.e. 5 entries with only one entry showing a 1 at the field RTYPE.
A sum(RTYPE) keeps returning 8 though, I don't understand why.
To be clear, i commented everyhting but the first load with the if condition to debug.
Its an AND condition that is needed.
I tried loading the new table as you suggested, but i don't really understand how i would be able to join the result into my existing table.
anbu: Why would i use the SumRTYPE before i create it below? How can i use the load statement without a from or resident?
so I tried this:
Load * ,
if (DATEITYP = 'R',1,0) as RTYPE
From xxx.qvd (qvd);
Sum(RTYPE) as SumRTYPE
Group By ID;
DROP table table1;
Where Field1 = 'x' and sumRTYPE>5;
DROP Table sum_rtype;
But i get an error "invalid expression" on the sum_rtype load. Anyone knows why?