Discussion Board for collaboration on QlikView Scripting.
I am trying to join two tables by a substring like is shown with a picture below, but I didn't have any luck with it.
Can you please help me.
As you can see in the picture, I want to join Table2 to Table1, where Field C is primary key and a substring to Field A from Table1.
I would appreciate some help and thank you on advance
I have worked out something. , yeah, it's a bit tricky.
Load * Inline [
'_'&FieldC&'_'&FieldD as New
SubField(MapSubString('Map', FieldA), '_',2) as FieldC,
Drop table Table1, Table2;
Note: Under-score ('_') as been taken to separate out the sub strings assuming there would not be any '_' in your key fields.
View solution in original post
If it always is character 2,3, and 4 from FieldA that should be used, then you can create a new field
Mid(FieldA, 2, 3) as Key
and make your join on this key.
But I suspect you want a more general solution, and if so, it could be a challenge, I would explore the option of creating a mapping table from table 2 and use mapsubstring() instead of a join.
You may use the following script and the table "Final" has the desired result.
LOAD * Inline [
Outer Join (Table1) LOAD * Resident Table2;
Drop Table Table2;
LOAD FieldA, FieldB, FieldC, FieldD Resident Table1
where Index(FieldA, FieldC) > 0;
Drop Table Table1;
Thank you all very much for your answers and effort. You helped me a lot