topic Re: wildmatch and leftjoin in QlikView

wildmatch and leftjoin

Peony — Sat, 16 Nov 2024 02:15:40 GMT

Hi All

I have dimention and fact tables like this

Dim.Acc

Acc	Round1	Round2
a	1	1
b	1	2
c	2	2
d	1	3


Fact.Acc_Live

Name	AccLive	Volume
we1	a	3456
er	c	446
dfr	a	4567
dvtw3	e	7

Now, in the script, I have a leftjoin like this

Fact.Acc_Live:
Load
Name,
AccLive,
Volume
from Fact.Acc_Live

Left Join (Fact.Acc_Live)
Load
  Acc as AccLive,
  Round1,
  Round2
from Dim.Acc

The thig is, now I need to leave in the Fact.Acc_Live table only that rows where AccLive = Acc. And i need to do it using WildMatch function.

Please, anyone have the idea how to do it?

Re: wildmatch and leftjoin

asinha1991 — Thu, 10 Oct 2019 12:51:28 GMT

you can do something like this for wildmatch

Patterntomatch:
Load Concat(chr(39)&'*'&pattern&'*'&chr(39),',') as conacc;
Load *inline [pattern
WORLD
DEER

];

let concatstring=peek('conacc',0,'Patterntomatch');

load * where WildMatch(CHECK,$(concatstring));
FinalTable:
Load *inline [CHECK
WORLDISABADPLACE
THISWORLDISBAD
DEEREATSGRASS
WHATTHEHECK

];

Re: wildmatch and leftjoin

Peony — Thu, 10 Oct 2019 12:54:48 GMT

Hi
Thank you for your help. Unfortunately I can't undertand how to use your example with the tables and fieldsname I have.

Re: wildmatch and leftjoin

asinha1991 — Thu, 10 Oct 2019 12:57:05 GMT

I will translate, but can you tell me why do you want to use wildmatch?

I see values in the fields are same

Re: wildmatch and leftjoin

asinha1991 — Thu, 10 Oct 2019 13:05:30 GMT

based on my understanding of question I was suggesting to do this

Patterntomatch:
Load Concat(chr(39)&'*'&Acc&'*'&chr(39),',') as conacc;

Load
  Acc ,
  Round1,
  Round2
from Dim.Acc

let concatstring=peek('conacc',0,'Patterntomatch');

Fact.Acc_Live:
load * where WildMatch(AccLive,$(concatstring));

Load
Name,
AccLive,
Volume
from Fact.Acc_Live ;

Re: wildmatch and leftjoin

Peony — Thu, 10 Oct 2019 13:17:21 GMT

Not really the same
AccLive field is contains "e" value that not exist in the Acc fileld. So, after applying wildmatch Fact table would not contain the row where AccLive field = 'e'

Re: wildmatch and leftjoin

Peony — Thu, 10 Oct 2019 13:16:49 GMT

Thank you!

Re: wildmatch and leftjoin

asinha1991 — Thu, 10 Oct 2019 13:46:18 GMT

in that case changing left to right join should be enough no?,

wildmatch is used to check wildcard

Re: wildmatch and leftjoin

Peony — Fri, 11 Oct 2019 06:12:20 GMT

Indeed.

Guess I am overthinking this task))