Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.