Qliksense - Best Replacement for SQL Select Case

Mon, 28 Mar 2016 19:33:17 GMT

Good afternoon everyone,

I've been monitoring the community feeds and have looked at multiple discussions on this subject but have not yet seemed to have found an answer to my own problem.

Among the kinds of data that I am tasked with creating reporting for is Web Sessions by Source & Channel. For example, Google|Organic or Bing|CPC, We have tools that I've written outside of Qlik that can process this data, but I'm hoping to re-invent the logic within the Data Load Editor of Qliksense.

IF I was to use SQL to do this kind of processing, I would use the following:

Source =

CASE

WHEN UrlReferrer like '%www.local.com%' THEN 'OTHER'

WHEN UrlQuery like '%gclid=%' OR UrlReferrer like '%google%' THEN 'GGL'

WHEN UrlQuery like '%utm_source=bing%' OR (UrlReferrer like '%bing.com%' OR UrlReferrer like '%www.msn.com%') THEN 'BING'

WHEN UrlReferrer like '%yahoo.com%' THEN 'YAHOO'

WHEN UrlReferrer like '%$(clienturl)%' THEN 'CORPORATE'

WHEN UrlQuery = '' And UrlReferrer = '' then 'OTHER'

ELSE 'OTHER'

END,

Channel =

CASE

WHEN UrlReferrer like '%www.local.com%' THEN 'REFERRAL'

WHEN UrlQuery like '%gclid=%' or UrlQuery like '%utm_medium%' THEN 'CPC'

WHEN UrlQuery = '' AND (UrlReferrer like '%google.c%' OR UrlReferrer like '%bing.com%' Or UrlReferrer like '%yahoo.com%' OR UrlReferrer like '%www.msn.com%') THEN 'ORG'

WHEN UrlQuery = '' and UrlReferrer = '' THEN 'DIRECT'

ELSE 'REFERRAL'

END,

However our hope was to drop the table data into QVD's and then process from there. I have attempted to use a combination of IF and Wildmatch, but so far have had no success.

	if(wildmatch(UrlReferrer,'%www.local.com%'),'OTHER',
	if(wildmatch(UrlQuery,'%gclid=%'),'GGL',

if(wildmatch(UrlReferrer,'%google%'),'GGL',

if(wildmatch(UrlQuery,'%utm_source=bing%'),'BING',

if(wildmatch(UrlReferrer,'%bing.com%','%www.msn.com%'),'BING',

if(wildmatch(UrlReferrer,'%yahoo.com%'),'YAHOO',

if(wildmatch(UrlReferrer,'%$(clienturl)}%'),'CORPORATE',

if(UrlQuery = '' And UrlReferrer = '','OTHER',

'OTHER')))))))) as Source,

	if(wildmatch(UrlReferrer,'%www.local.com%'),'REFERRAL',
	if(wildmatch(UrlQuery,'%gclid=%','%utm_medium%'),'CPC',
	if(UrlQuery = '' AND wildmatch(UrlReferrer,'%google.c%','%bing.com%','%yahoo.com%','%www.msn.com%'),'ORG',
	if(UrlQuery = '' AND UrlReferrer = '','DIRECT',
	'REFERRAL'))))) as Channel,

When I run the code I get no errors but I consistently get OTHER and REFERRAL as the results...so it's always falling to the ELSE aspect if the originating IF statement.

URLQuery is a long string, that could look like the following:
utm_source=bing&utm_medium=cpc&utm_campaign=Fort-Worth

UrlReferrer is also a long string that could look like the following (or a thousand other iterations - hence Other):

http://www.google.ca/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0CEEQFjAD

Anybody have an idea of what I'm doing wrong...or is there a better way of handling the kind of data that I'm looking at?

Thanks!

Tim Norton

www.dacgroup.com/

Re: Qliksense - Best Replacement for SQL Select Case

Nicole-Smith — Mon, 28 Mar 2016 20:04:42 GMT

In QV the wildcard character is * for multiple characters or ? for a single character, so using the % character will not work. Just swap out the wildcards, and you should be good to go.

Re: Qliksense - Best Replacement for SQL Select Case

Tue, 29 Mar 2016 14:42:34 GMT

Thanks very much Nicole, you saved me a lot of aggravation. There was a time I wouldn't have thought twice about using * over % as a wildcard...back when I used Microsoft Access more. Now that I use SQL Server almost 100% of the time it didn't even dawn on me to try it.

I've noticed a couple features in Qlik that seems to echo early Microsoft technology. I will do a separate posts on those once I've completed testing.

Cheers!

Tim

topic Qliksense - Best Replacement for SQL Select Case in App Development

Qliksense - Best Replacement for SQL Select Case

Re: Qliksense - Best Replacement for SQL Select Case

Re: Qliksense - Best Replacement for SQL Select Case