topic Re: If statement with a group by ??? in App Development

If statement with a group by ???

mikegauntlett — Sat, 16 Nov 2024 05:01:28 GMT

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.

Re: If statement with a group by ???

sunny_talwar — Fri, 09 Aug 2019 15:00:04 GMT

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;

Re: If statement with a group by ???

jensmunnichs — Fri, 09 Aug 2019 15:04:35 GMT

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.