Discussion Board for collaboration on QlikView Scripting.
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.
Table A:Id, Name1, James2, Harry3, Sara4, Tina5, David
Table B:ID, NameB12, TEXT Tina22, TEXT Bryan28, TEXT James34. TEXT Harry 145, TEXT Alan
TABLE Z:1, James2, Harry3, Sara4, Tina5, David12, Tina28, James34, Harry
right, let's try to add some data cleansing and then do the rest of job.
TableA:load * Inline[Id, Name1, James2, Harry3, Sara4, Tina5, David];
TableB:load *Inline[Id, NameB12, ABVD-Tina_dhsfd1222, 1298 asdf_Brian28, James 123_Copi34, TEXT Harry 1 dfaf45, TEXT Alan];
//data transformation and cleansing//autoconcatenate table Afor i=0 to NoOfRows('TableA')-1
let sourceName = '*'&Peek('Name',$(i),'TableA')&'*';for j=0 to NoOfRows('TableB')-1TableC:load DistinctId, Replace('$(sourceName)','*','') as NameResident TableBwhere WildMatch(NameB,'$(sourceName)');next j;next i;drop tables TableB;EXIT SCRIPT;
View solution in original post
hope you are doing well. Please try following:
tableA:load * Inline[Id, Name1, James2, Harry3, Sara4, Tina5, David];
outer join (tableA)
tableB:load *where not Exists(Name);
loadId,subfield(NameB,' ',2) as Name
Inline[Id, NameB12, TEXT Tina22, TEXT Bryan28, TEXT James34, TEXT Harry 145, TEXT Alan];
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
I was thinking about this double loop this weekend.
Glad you find a good way for my problem.
Thanks a lot