topic Re: Wildmatch in joining keys in QlikView

Wildmatch in joining keys

Anonymous — Wed, 20 Dec 2017 16:46:08 GMT

Hi All,

Please let me know how to use wildmatch in joining keys

example

Table1:

Region, User

|Corporate Sector|, A

|Sector 1|, B

|Sector 2|, C

FactTable:

Region, Country

Corporate Sector Asia Pac Value Y, India

Sector A Europe N, US

If i use wildmatch in Joining keys when user A is selected fact table country India should be selected . i have to do this in script part

Thanks in advance

Re: Wildmatch in joining keys

Anil_Babu_Samineni — Tue, 26 Dec 2017 12:27:33 GMT

Interesting !!! Checking ...

Re: Wildmatch in joining keys

Anil_Babu_Samineni — Tue, 26 Dec 2017 12:58:27 GMT

Can you try this?

FactTable:

Mapping Load * Inline [

Region, Country

Corporate Sector Asia Pac Value Y, India

Sector A Europe N, US

];

Table1:

LOAD User, If(SubStringCount(ApplyMap('FactTable', Region), Region), ApplyMap('FactTable', Region)) as Region,

If(Region = ApplyMap('FactTable', Region), 'Yes', 'No') as OWM_Flag;

LOAD PurgeChar(Region, '|') as Region, User Inline [

Region, User

|Corporate Sector|, A

|Sector 1|, B

|Sector 2|, C

];

Re: Wildmatch in joining keys

pipuindia99 — Tue, 26 Dec 2017 16:15:24 GMT

Where did you use "Wildmatch" function?

Re: Wildmatch in joining keys

Anil_Babu_Samineni — Wed, 27 Dec 2017 02:12:10 GMT

Title looks Wildmatch but not sure why it's needed

Re: Wildmatch in joining keys

pradosh_thakur — Wed, 27 Dec 2017 03:31:16 GMT

If you need wildmatch anyhow then try this

Table:
Load * Inline [

Region, Country

Corporate Sector Asia Pac Value Y, India

Sector A Europe N, US

];

join(Table)

LOAD PurgeChar(Region, '|') as Region1, User Inline [

Region, User

|Corporate Sector|, A

|Sector 1|, B

|Sector 2|, C

];

Table1:

noconcatenate LOAD *

Resident Table

where WildMatch(Region, '*' & Region1 & '*')

;

DROP Table Table ;

PS:I will prefer the applymap any day rather than join and wildmatch.

Re: Wildmatch in joining keys

Anonymous — Wed, 27 Dec 2017 04:03:35 GMT

Why do you want function Wildmatch??

I prefer function ApplyMap rather join

Re: Wildmatch in joining keys

tresB — Wed, 27 Dec 2017 05:08:37 GMT

Using MapSubString() it would be little easier. Try like:

Map:

Mapping Load

       Purgechar(Region, '|') as Region,

      '#'&User&'@' as User

Inline [

Region, User

|Corporate Sector|, A

|Sector 1|, B

|Sector 2|, C

AAAA, XX

];

Table1:

LOAD

       *,

      TextBetween(MapSubString('Map', Region),'#','@') as User;

LOAD * Inline [

Region,                             Country

Corporate Sector Asia Pac Value Y, India

Sector A Europe N,                  US

AAAABBB,                              Canada

];

Re: Wildmatch in joining keys

Anil_Babu_Samineni — Wed, 27 Dec 2017 05:16:45 GMT

This one makes clear? I believe All users should be present rather A & XX.