in(),isnull, not in() functions equivalent in Qlik view

[#1]

Hi Team,

below Query mysql Query in the script..how can i use in Qlik view

I tried using wildmatch and if ,not match ..i am getting out put in wrong way

SELECT

CASE

WHEN bookingsource='web' AND saleschannel='web' AND userid IS NULL THEN 'Online'

WHEN bookingsource='web' AND saleschannel='web' AND userid NOT LIKE '%MOBILE#%') AND userid IS NOT NULL THEN 'AssitSales'

WHEN bookingsource IN('XX','XXX','XXXX','XXXX') AND saleschannel='WEB' THEN 'ONLINEPARTNERS'

WHEN userid LIKE '%MOBILE%' AND userid IN ('MOBILEAPPDRIOD','MOBILEAPPIOS','MOBILEWINDOWS') THEN 'MOBILEAPP'

WHEN userid LIKE '%MOBILE%' AND userid NOT IN ('MOBILEAPPDRIOD','MOBILEAPPIOS','MOBILEWINDOWS') THEN 'MOBILE WEB'

WHEN userid LIKE '%MOBILE%' THEN 'TOTALMOBILE'

WHEN saleschannel IN ('Callcenter','HD') THEN 'Offline'

WHEN bookingsource='agents' AND saleschannel='agents' THEN  'rbcorpsales'

WHEN saleschannel IN ('AgentsAPI1','AgentsAPI2') THEN 'rbpartnersales' END AS Channel,SUM(transactions)

FROM

fact_table_sales

thanks

Kalyan

[#2]

Kaylan,

- For IN() function, use QV's MATCH, which goes, for instance, like this: MATCH(bookingsource,'XX','XXX','XXXX','XXXX'). Of course, for NOT IN, just use NOT MATCH(...)

- For NULL handling, you may use NULL() function. For instance: userid <> NULL(). Alternatively, you can use ISNULL(userid).

[#3]

You can run the exact query in QlikView using SQL Select.

or you can pull just the columns from the table using SQL Select

and then do resident load from this table.

When you are doing resident load at that time you can use Match or Wild Match as they are QlikView functions and will work only with Load statements.

From your post looks like you have already done so.

You shared your SQL query. Can you share how you are using it in QlikView.

I am using Oracle and i can paste my exact SQL query in Qlikview and get results.

[#4]

See below link on how IN and NOT IN used in QlikView

http://www.learnallbi.com/using-in-clause-in-qlikview/

[hic]

For NULL handling you should never use relational operators and Null(), e.g.

     Field <> Null()

since this never will evaluate to FALSE. NULL <> NULL evaluates to NULL.

Instead, you should use one of the two following tests:

     IsNull( Field )    - which tests for NULL

     Len( Trim( Field ) ) = 0    - which tests for empty fields (NULL or blank or zero-length string)

See more on NULL handling in QlikView

HIC

[#6]

if(WildMatch(saleschannel,'Online') and not match(userid,'*MOBIle*'),'web') as channel1

above query i used ..but still i am facing a problem ..in the data i can see mobilesales

please let me know any changes i have to make