topic Re: Joining two tables based on wildcard values. in QlikView

Joining two tables based on wildcard values.

Tue, 25 Mar 2014 17:21:39 GMT

Hi all,

is it possible to make a join using wildcard values? With this code will be easier to understand what I am trying to get:

T1:

LOAD * INLINE [

Account

70%

800%

];

T2:

LOAD * INLINE [

Account, Import

6, 1

7000000, 2

8000000, 3];

Inner Join(T1)

Load

Account,

Import

Resident T2;

Drop Table T2;

This code wroked as expected (not how I liked). What I want to get is to join but using wildcard values (70% <-> 7000000) instead of perfect matchs. is it possible? is there any other way? I know Wildmatch() function and the like, but I need the wildcard in the join.

Thanks so much !!!

Re: Joining two tables based on wildcard values.

Anonymous — Tue, 25 Mar 2014 17:32:33 GMT

See an example, maybe it will help you to move in the right direction.

Re: Joining two tables based on wildcard values.

Tue, 25 Mar 2014 18:15:01 GMT

thanks for your answer. It is useful.

However, it's not exactly what I am looking for because it is not replicating the behavior of the wildcard. I mean, if in your solution I change "7000000" by "7060000", the search string "70%" doesn't pick "7060000" up as it should be if it were a real wildcard.

Re: Re: Joining two tables based on wildcard values.

Anonymous — Tue, 25 Mar 2014 18:41:25 GMT

I think the second version is closer to what you want. It is not effective though for a large data set because I'm creating outer join in an intermediate step, and it could be a rather large table.

Re: Re: Joining two tables based on wildcard values.

Anonymous — Tue, 25 Mar 2014 18:56:33 GMT

One more version - no outer join, that is no large table. Instead of this, I use loop. Reload will be slower but takes less memory.

Again, it matters only for a large data set.

Re: Joining two tables based on wildcard values.

rwunderlich — Tue, 25 Mar 2014 19:36:25 GMT

I would use "Wildcard Mapping" to do mapping and then join -- or perhaps the mapping is enough. You can find examples of Mapping with Wildcards here

http://qlikviewcookbook.com/recipes/download-info/mapping-with-wildcards/

or as a subroutine in Qlikview Components http://qlikviewcomponents.org

-Rob

Re: Joining two tables based on wildcard values.

Wed, 26 Mar 2014 09:14:11 GMT

Thanks a lot Michael !!!

Re: Joining two tables based on wildcard values.

Wed, 26 Mar 2014 09:15:03 GMT

Thank you !! I'll give it a try. In any case, good to know that it is possible to do this.