not exists

[#1]

Hi,

I want to exclude few ticketids while calculating the SLA. I am not able to find the exact function to be used. What is Qlikview equivalent function of Not in.

The ticketid will be provided through Input Box by the user at the runtime. Sometimes it will be one ticketid and sometimes it may be more than one ticketid or zero ticket ids.

I want to know

1. What is the Equivalent function of NOT IN in Qlikview.

2. If multiple values are provided in the input box how to include the multiple values in the not in function...How to invoke the muliple value variable accordingly in the not in function

[pover]

The equivalent to not in would be not match(Field,Values) where value can be a list of multiple values so you have to make a variable that forms a list like this 'Value1','Value2','Value3',etc.

To make the list you can use an expression like this where chr(39) are single quotes

=chr(39) & concat(Ticketids,chr(39) & ',' & chr(39)) & chr(39)

You can assign that expression to a variable and so the final expression would be

not match(Field,$(Variable_List))

Regards.

[#3]

Hi karl,

May be this is simple Question ..But still i didnt understand the expression =chr(39) & concat(Ticketids,chr(39) & ',' & chr(39)) & chr(39) .

How to use this in the input box.

[Anonymous]

Hi,

If you want to pass text values to an expression. In the concat function you have to specify a separator, you will need to force single quotes to appear in the expression, thats what the chr(39) is doing. Due to the setup of the concat function you won't be able to have ''' after to specify a separator whereas using chr(39) works.

You can also use the DISTINCT operator to let you pass multiple values......

Concat(distinct FIELDNAME, ';')

I would think about using a data island(a small table with a distinct list of your desired values, that is not joined to the data model in any way) in your script rather than an input box. This way you are controlling what the users can select and also makes the user interface alot easier to use.

Cheers.

Ad.

[pover]

How is the user inputing the multiple values in the input box? Are they manually putting the comma between the values?

The concat is an optional step and work better with multiple selections over fields which can come from a data island like aby says, but tell us first what the user is doing to give you the best answer.

Regards.

[johnw]

My understanding is that the user would type zero to many ticket IDs in an input box, presumably with commas between them, but almost certainly without single quotes. If so, this appears to function as "not in":

len(ExcludeTickets)=0 or not match(TicketID,$(=chr(39)&replace(ExcludeTickets,',',chr(39)&','&chr(39))&chr(39)))

I think a better option would be to train the users to select the values they don't want, right click, then click on select excluded. That knowledge will help them with multiple applications, not just this one.

[pover]

I completely agree with John's last point.

[#8]

Thanks for your responses.

We can use the select excluded but the number of ticketid will be minimum 1000 and they have to exclude some 10 tickets. More over we need to keep track of the tickets excluded.

The difficult part either using input box or the select excluded is keeping track of excluded Ticketid.