topic Re: not in function.. in QlikView

not in function..

Fri, 04 Apr 2014 11:09:12 GMT

Hi,

How to use Not in function in Qlik view

Example Query of Sql

select

Case when saleschannel='online' and userid not in ('MobileWap','Mobile APP') then 'Online_Sales'

End Channel

from temp_Table

Above Query How Can use in Qlik View ..

Re: not in function..

sushil353 — Fri, 04 Apr 2014 11:15:11 GMT

hi,

For NOT IN .. MATCH() is equivalent function in qlikview..

so you can use : Match(userid,'MobileWap','Mobile APP')

if (saleschannel='online' and Match(userid,'MobileWap','Mobile APP'), 'Online_Sales')

HTH

sushil

Re: not in function..

Fri, 04 Apr 2014 11:16:59 GMT

Like this

Load * from temp_Table

Where saleschannel='online' and Match(userid,'MobileWap','Mobile APP') <=0;

Re: not in function..

Fri, 04 Apr 2014 11:35:59 GMT

how can use for the below Query

case when saleschannel='webdirect' and (userid null or userid not like '%MOBILE%') then 'Online Channel'

end Channel

kalyan

Re: Re: not in function..

Fri, 04 Apr 2014 11:46:57 GMT

Load * From TempTable

Where saleschannel='webdirect' and (userid<>null() or Wildmatch(userid, '?MOBILE?') <=0);

Re: not in function..

arjunkrishnan — Fri, 04 Apr 2014 12:05:51 GMT

Hai Kalyan ,

I Hope You This Will Most Helpful For Ur Understanding Concept of NOT IN How Working With Wild Match Concept..

Normal Table:

LOAD [Sno ] ,

[product ] ,

Sales

FROM

sAD.xls

(biff, embedded labels, table is [Sheet1$]);

Wild Match(IN)

NoConcatenate

Table1_In_WildMatch:

LOAD [Sno ] as Num,

[product ] as Prod,

Sales as Sales1

FROM

sAD.xls

(biff, embedded labels, table is [Sheet1$])

Where WildMatch([product ],'P','Q');

WildMatch(NOT IN)

NoConcatenate

Table1_Not_In_WildMatch:

LOAD [Sno ] as N_Sno,

[product ] as N_Prod,

Sales as N_Sales

FROM

sAD.xls

(biff, embedded labels, table is [Sheet1$])

Where not WildMatch([product ],'P','Q');

ArjunKrish

Re: not in function..

sivarajs — Fri, 04 Apr 2014 12:06:09 GMT

load

If( saleschannel='online' and not match(userid,'MobileWap','Mobile APP')=-1,'Online_Sales') as Channel

from temp_Table

Re: not in function..

mangalsk — Fri, 04 Apr 2014 12:24:15 GMT

Hello,

load Online_Sales,saleschannel,userid

from table where saleschannel='online' and not WildMatch(userid,'MobileWap','Mobile APP');

Re: not in function..

Fri, 04 Apr 2014 12:30:02 GMT

Hi,

It can be:

LOAD If(saleschannel='online' and Match(userid,'MobileWap','Mobile APP')=0,'Online_Sales') as Channel

From TempTable (or Resident TempTable)

Hope this helps.