topic Re: Multiple if in expression in QlikView

Multiple if in expression

clausbarlose — Wed, 14 Mar 2012 13:20:05 GMT

Hi,

I am having difficulties with creating a listbox to select tickets from our management system.We look at our tickets in a monthly perspective and like to follow tickets created in a specific month or tickets closed in the same month. Users inputs StartDate and EndDate. A ticket always has a creation date but not always a closed date (ie. field is null() ).I have made a listbox with the following expression:

=if ( not isnull (TicketClosed),

if ( (TicketCreated >= StartDate and TicketCreated <= EndDate) or

(TicketClosed >= StartDate and TicketClosed <= EndDate), 'Yes', 'No'),

if (TicketCreated >= StartDate and TicketCreated <= EndDate, 'Yes', 'No'))

Listbox title is obvoiusly "Tickets created or closed in period".

Listbox shows the proper Yes and No, but I end up with all tickets but the ones that have no TicketClosed date, ie. TicketClosed is null().

Anyone with an opinion on this or event better a sugestion for doing it otherwise?

Btw - we are on QV 9

All the best!

Claus

Multiple if in expression

CELAMBARASAN — Wed, 14 Mar 2012 13:27:02 GMT

Hi,

You mean you didn't get the results for Ticked closed date is null?

Celambarasan

Multiple if in expression

clausbarlose — Wed, 14 Mar 2012 13:31:57 GMT

When clicking Yes in the listbox I get the tickets where CreationDate or ClosedDate are in the correct interval, but are missing tickets that have a null() value in ClosedDate.

Tickets can be created a lot earlier in which case we like to track them by the ClosedDate.

The selection acts like a filter.

Multiple if in expression

CELAMBARASAN — Wed, 14 Mar 2012 13:40:58 GMT

Hi,

Check whether it has null value or Empty value.

Celambarasan

Multiple if in expression

clausbarlose — Wed, 14 Mar 2012 14:08:03 GMT

Hi,

I took a couple of samples and QV says field is null(). I put this in the table view:

=if ( isnull(TicketClosed), 'Null()', 'Not Null()')

- and QV says 'Null().

Claus

Multiple if in expression

CELAMBARASAN — Wed, 14 Mar 2012 14:27:15 GMT

Hi,

Try this method

Before loading the table use NullAsValue like below

NullAsValue TicketClosed;

Load

From Datasource;

In list box use expression as

=if ( TicketClosed<>'',

if ( (TicketCreated >= StartDate and TicketCreated <= EndDate) or

(TicketClosed >= StartDate and TicketClosed <= EndDate), 'Yes', 'No'),

if (TicketCreated >= StartDate and TicketCreated <= EndDate, 'Yes', 'No'))

Celambarasan

Re: Multiple if in expression

Wed, 14 Mar 2012 14:38:35 GMT

I think the enclosed kindly provided by John Witherspoon could be usefull to you (you shall put it in the load). It basically lets you choose only one date regardsless of whether the ticket has started or ended there.

Multiple if in expression

clausbarlose — Wed, 14 Mar 2012 14:38:46 GMT

Hi,

I'll try this and post a reply. Thanks for your help!

/Claus

Re: Multiple if in expression

clausbarlose — Wed, 14 Mar 2012 14:43:36 GMT

Hi - thanks for the post. Unfortunately this can't be done in the script, as user inputs start and end date in the GUI.

/Claus

Re: Multiple if in expression

clausbarlose — Wed, 14 Mar 2012 18:52:02 GMT

Hi Celambarasan

The NullAsValue did the job, thanks alot! - Funny though, that the table view said that value was null. Apparently QV in some cases has some challenges with nulls - or am I wrong?

Thanks DK_ASN too!

Best regards,

Claus

Multiple if in expression

CELAMBARASAN — Thu, 15 Mar 2012 04:47:05 GMT

Hi,

Yes.QV won't include null values to the list box.

Celambarasan