Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Partner
Partner

Field Value exists in an other field with 'like' comparaison

Hi,

I am struggling with a simple request.

Table A is the main table with clean data.

Table B need to refer to table A. The field 'Name' is the only possible association.

We must compare each value from field 'Name' Table A to each value that has in field 'NameB' Table2.

I tried with EXISTS and LIKE (with wildcards) together, no success. Even with a WILDMATCH. 

Any way  to make it easy in scripting. 

 

Example:

Table A:
Id, Name
1, James
2, Harry
3, Sara
4, Tina
5, David

 

Table B:
ID, NameB
12, TEXT Tina
22, TEXT Bryan
28, TEXT James
34. TEXT Harry 1
45, TEXT Alan

 

Excepting Result:

TABLE Z:
1, James
2, Harry
3, Sara
4, Tina
5, David
12, Tina
28, James
34, Harry

Labels (4)
1 Solution

Accepted Solutions
mato32188
Contributor III

Re: Field Value exists in an other field with 'like' comparaison

Hi T_donnet,

right, let's try to add some data cleansing and then do the rest of job.

TableA:
load * Inline[
Id, Name
1, James
2, Harry
3, Sara
4, Tina
5, David
];


TableB:
load *
Inline[
Id, NameB
12, ABVD-Tina_dhsfd12
22, 1298 asdf_Brian
28, James 123_Copi
34, TEXT Harry 1 dfaf
45, TEXT Alan
];


//data transformation and cleansing
//autoconcatenate table A
for i=0 to NoOfRows('TableA')-1

let sourceName = '*'&Peek('Name',$(i),'TableA')&'*';

for j=0 to NoOfRows('TableB')-1


TableC:
load Distinct
Id, Replace('$(sourceName)','*','') as Name
Resident TableB
where WildMatch(NameB,'$(sourceName)');

next j;

next i;

drop tables TableB;

EXIT SCRIPT;

Best regards,

Martin

View solution in original post

4 Replies
mato32188
Contributor III

Re: Field Value exists in an other field with 'like' comparaison

Hi T_donnet,

hope you are doing well. Please try following:

tableA:
load * Inline[
Id, Name
1, James
2, Harry
3, Sara
4, Tina
5, David
];

outer join (tableA)

tableB:
load *
where not Exists(Name);

load
Id,
subfield(NameB,' ',2) as Name

Inline[
Id, NameB
12, TEXT Tina
22, TEXT Bryan
28, TEXT James
34, TEXT Harry 1
45, TEXT Alan
];

BR

Martin

Highlighted
Partner
Partner

Re: Field Value exists in an other field with 'like' comparaison

Hi Martin,

Thanks for your answer.

The thing is for values in the field 'NameB', there aren't structure like the example.
It can be anywhere. (ABVD-Tina_dhsfd12 ; 1298 asdf_Harry; James 123_Copi)

I tried with => Name LIKE *NameB* 
It didn't work

mato32188
Contributor III

Re: Field Value exists in an other field with 'like' comparaison

Hi T_donnet,

right, let's try to add some data cleansing and then do the rest of job.

TableA:
load * Inline[
Id, Name
1, James
2, Harry
3, Sara
4, Tina
5, David
];


TableB:
load *
Inline[
Id, NameB
12, ABVD-Tina_dhsfd12
22, 1298 asdf_Brian
28, James 123_Copi
34, TEXT Harry 1 dfaf
45, TEXT Alan
];


//data transformation and cleansing
//autoconcatenate table A
for i=0 to NoOfRows('TableA')-1

let sourceName = '*'&Peek('Name',$(i),'TableA')&'*';

for j=0 to NoOfRows('TableB')-1


TableC:
load Distinct
Id, Replace('$(sourceName)','*','') as Name
Resident TableB
where WildMatch(NameB,'$(sourceName)');

next j;

next i;

drop tables TableB;

EXIT SCRIPT;

Best regards,

Martin

View solution in original post

Partner
Partner

Re: Field Value exists in an other field with 'like' comparaison

Hi Mato,

I was thinking about this double loop this weekend.

Glad you find a good way for my problem.

Thanks a lot

Sincerly,

Theo