topic Re: Adding AND Function in QlikView

Adding AND Function

Mon, 01 Dec 2014 14:14:55 GMT

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

Re: Adding AND Function

Anonymous — Mon, 01 Dec 2014 14:21:10 GMT

Hi Andreas,

Use :

+ : union of sets

* : intersection of sets

Depending on you requirements.

Regards

Neetha

Re: Adding AND Function

anbu1984 — Mon, 01 Dec 2014 14:26:25 GMT

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

Re: Adding AND Function

giacomom — Mon, 01 Dec 2014 14:28:52 GMT

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

Re: Adding AND Function

Mon, 01 Dec 2014 14:47:01 GMT

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))))

Re: Adding AND Function

simenkg — Mon, 01 Dec 2014 14:53:51 GMT

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

Re: Adding AND Function

Mon, 01 Dec 2014 15:10:10 GMT

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;

Re: Adding AND Function

giacomom — Mon, 01 Dec 2014 15:17:46 GMT

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

Re: Adding AND Function

simenkg — Mon, 01 Dec 2014 15:37:26 GMT

LOAD * ,

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

SQL SELECT *

FROM database.task;

Re: Adding AND Function

Tue, 02 Dec 2014 07:48:25 GMT

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 ?

Re: Adding AND Function

simenkg — Tue, 02 Dec 2014 08:43:10 GMT

You can join the Category into the fact table and then do the load.

Tasks_t1:
Load * from Tasks.qvd (qvd);

Categories:
Load * from Categories.qvd (qvd);

Left join (Tasks_t1)

Load %Task_Key, Category as TestCategory resident Categories;

noconcatenate

Tasks:

Load *,

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

resident Tasks_t1;

Drop table Tasks_t1;

Drop field TestCategory;

Re: Adding AND Function

Tue, 02 Dec 2014 09:39:08 GMT

Thank you!