Applying status based on counts

[DSRScape]

Hi,

I have project references with each having a client reference. Clients often have multiple projects.

Each project is either Active or Complete.

If a client has at least 1 active project, I want to assign a client status of Active.

If they only have complete projects, their client status should be Complete.

I think this should be straightforward but I can't figure it out so hope someone can help!

Many thanks,

David

[rwunderlich · Accepted solution]

Do you want to do this in the front end or in script?  If in the front end, given a chart with Client as Dimension you could show status as:

=if(Count({<ProjectStatus={'Active'}>}ProjectId) > 0, 'Active', 'Inactive')

In the script, something like:

Projects:
LOAD * Inline [
ClientId, ProjectId, ProjectStatus
1, 1, Active
1, 2, Complete
1, 2.5, Active
2, 3, Complete
2, 4, Complete
3, 5, Active
]
;

Status:
LOAD
ClientId,
if (ActiveCount > 0, 'Active', 'Complete') as ClientStatus
;
LOAD
ClientId,
Sum(if(ProjectStatus = 'Active', 1)) as ActiveCount
Resident Projects
Group by ClientId
; 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[DSRScape]

Many thanks Rob for nailing it - so quickly too!

That's going to be a useful technique for other scenarios too.

Thanks again,

David