If statement with a group by ???

[mikegauntlett]

This should be really basic, but i can't seem to figure it out 

So I have Projects , Projects then have invoices, invoices then have outstanding amounts. All have their own fields

[Project,Invoice,outstanding amount,

Project1,abc,65000

Project1,def,0

Project2,ghi,0]

How can I write an If statement to tell me whether a Project has an outstanding amount. Because when I write basics like 

 If(Len(outstanding_amount)>2,'YES','NO') AS XYZ 

It works but does it per invoice, not rolled up to a project level. 

[sunny_talwar]

Try this

Table:
LOAD * INLINE [
    Project, Invoice, outstanding amount
    Project1, abc, 65000
    Project1, def, 0
    Project2, ghi, 0
];

Left Join (Table)
LOAD Project,
	 If(Sum([outstanding amount]) > 0, 'YES', 'NO') as XYZ
Resident Table
Group By Project;

[jensmunnichs]

You need to use at least one aggregation function (Sum, count, etc.) to be able to group values (otherwise, Qlik won't know how to combine the values in order to group them).

Try this:

Data:
LOAD * INLINE
[Project,Invoice,outstanding amount,
Project1,abc,65000
Project1,def,0
Project2,ghi,0];

Final:
LOAD
Project,
If(Sum([outstanding amount])>0, 'Yes', 'No') as OutstandingFlag
Resident Data
Group By Project;

Drop table Data;

Note that if your [outstanding values]  field can have negative values, these could cancel out the positive values. If you don't want this to happen, you'll have to change the formula a little bit (for example, add an if to the sum to only sum positive values). If that's the case and you need any help with that, let me know.