Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Not applicable

Adding AND Function

Hello,

hope someone can help me with my problem.

First of all i wanted to count all my tasks which are longer in process then the desired duedate.

I created this code and it worked

=((Count(if(duedate<= Today(),duedate)))) - (Count({<[Status (gen)]={'done'}>}[Status (gen)]))

So it counts all tasks with an duedate shorter then the actual date, excluding all tasks that are already done.

[Status (gen)] can be "done" or "in progress"

Now i want to add an AND Function:

For example: AND [Category] = {' Supply Chain'}

i tried to add my AND but the code did not work

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Adding AND Function

Don't do this in set analysis. Do it in your script.

Count/Sum/Avg ( if()) is a performance killer and is something you should NEVER do.

In stead create a flag in your scipt like this:

load .

.

.

if(duedate<= Today() and [Status (gen)] <> 'done' and [Category] = 'Supply Chain', 1, 0) as OverdueFlag

from

Table Tasks.qvd (qvd) ;

Then you use set analysis like this:

count({<OverdueFlag={1}>}Task)

Regards

SKG

View solution in original post

11 Replies
Highlighted
Honored Contributor

Re: Adding AND Function

Hi Andreas,

Use :

+ : union of sets

*  : intersection of sets

Depending on you requirements.

Regards

Neetha

Highlighted
Honored Contributor III

Re: Adding AND Function

=((Count(if(duedate<= Today(),duedate)))) - (Count({<[Status (gen)]={'done'}>*<[Category] = {' Supply Chain'}>}[Status (gen)]))

Highlighted
Partner
Partner

Re: Adding AND Function

Hi Andreas,

you can add conditions in AND in your set analysis expression by adding them comma-separated inside the < > brackets like this:

=((Count(if(duedate<= Today(),duedate)))) - (Count({< [Category] = {' Supply Chain'} , [Status (gen)]={'done'}>}[Status (gen)]))



Regards,


Giacomo

Highlighted
Not applicable

Re: Adding AND Function

Ah ok!

So i can use the * or I add a , inside the >< brackets, got that

And how i can use the AND function on the first Count so it can be part of the condition?

Count(if(duedate<= Today(),duedate))))

Highlighted
Partner
Partner

Re: Adding AND Function

Don't do this in set analysis. Do it in your script.

Count/Sum/Avg ( if()) is a performance killer and is something you should NEVER do.

In stead create a flag in your scipt like this:

load .

.

.

if(duedate<= Today() and [Status (gen)] <> 'done' and [Category] = 'Supply Chain', 1, 0) as OverdueFlag

from

Table Tasks.qvd (qvd) ;

Then you use set analysis like this:

count({<OverdueFlag={1}>}Task)

Regards

SKG

View solution in original post

Highlighted
Not applicable

Re: Adding AND Function

Thank you.

I tried it but there is an error at the comma after 'Supply Chain'

if(duedate<= Today() and [Status (gen)] <> 'done' and [Category] = 'Supply Chain', 1, 0) as OverdueFlag



LOAD

.

.

.

`Category`;

if(duedate<= Today() and [Status (gen)] <> 'done' and [Category] = 'Supply Chain', 1, 0) as OverdueFlag

SQL SELECT *

FROM database.task;

Partner
Partner

Re: Adding AND Function

Hi,

you can add the condition in the first Count using the expression:

     =Count({$ <[Category] = {' Supply Chain'} , duedate={"<=$(#vToday)"}>} duedate)

where vToday is the variable you created before in your script.

In this case you can remove the if inside a Count/Sum/Avg calculation as Simen Kind Gulbrandsen suggests.

Regards,

Giacomo

Highlighted
Partner
Partner

Re: Adding AND Function

LOAD * ,

if(duedate<= Today() and [Status (gen)] <> 'done' and [Category] = 'Supply Chain', 1, 0) as OverdueFlag;

SQL SELECT *

FROM database.task;

Highlighted
Not applicable

Re: Adding AND Function

Thank you very much, it worked perfectly!

The only thing I wonder now is, can I use this out of my LOAD code?

load .

.

.

if(duedate<= Today() and [Status (gen)] <> 'done' and [Category] = 'Supply Chain', 1, 0) as OverdueFlag

from

Table Tasks.qvd (qvd) ;

So lets say I have another LOAD code where I add something.

I cant use the if(duedate<= Today() and [Status (gen)] <> 'done' and [Category] = 'Supply Chain', 1, 0) as OverdueFlag

because I cant use an attribute which is not in my active LOAD code.

Just to know, what if my duedate comes from another LOAD code then my Category ?