topic LIKE in join in QlikView

LIKE in join

Fri, 17 Dec 2010 12:24:01 GMT

Hi Everyone,

How can I replicate the following SQL in QlikView (problem is the like statement):-

select top 1 1
from #ABC a
join #DEF b
on a.Product like '%' + b.Product + '%'

Since QV will join it automatically, how can I join it using LIKE?

LIKE in join

tanelry — Fri, 17 Dec 2010 15:26:16 GMT

If your data source is SQL then you could use sql syntax to first load the like-table with all possible matches from sql into a QV mapping table,
then use applymap() in QV script.

LIKE in join

Fri, 17 Dec 2010 16:15:19 GMT

Hi!

In QV instead [%] in SQL

so expression in SQL a.text like '%default%'

in QV will be a.text like '*default*'

Hope it will help.

LIKE in join

Sat, 18 Dec 2010 04:14:12 GMT

My data source now is QVD... so can't use the SQL syntax. 😕

LIKE in join

Mon, 20 Dec 2010 09:11:33 GMT

I meen that in QV you can use star ( * ) instead ( % )

You can use it anywhere in QV, in expression, for example.

LIKE in join

Tue, 21 Dec 2010 02:09:10 GMT

Hi,

Thanks for the suggestion. But I'm actually referring to Tanel's post.

As for your suggestion, my issue is that I need to do the JOIN in script. As you know, QlikView joins the tables automatically based on the same field name. How can I use * to do a fuzzy/LIKE join?

LIKE in join

john_duffy — Tue, 21 Dec 2010 16:59:31 GMT

Hello.

The wildmatch command performs a comparison and allows wildcard characters. I have attached a small sample application using this command when joining two tables.

Let me know if this helps.

John.