Load field from joining table based on if condition satisfied from sub string value from left table
I am new to QLIKVIEW. I am implementing one functionality and I am stuck at one place. The scenario is as below:
There are two tables and i need to implement a left join among them. The conditions are as below:
Table A: having fields: Field1(Key1), Field2, Field3, Field4, Field5
Table B: Having fields: FieldA(Key2), FieldB, FieldC, FieldD
I want to add new fields in final table based on some calculations. New fields to be added as: NewField1, NewField2
Conditions is: if (mid(Field2,2,1)=B) then
NewField1='00000'&''&mid(Field2,4,3) /* For example it will become 00000653*/
NewField2=TableB.FieldB(NewField1) /* Now 00000653 value maps to key of Table B and against to 00000653, FieldB value of Table B should be assigned to NewField2. Here 00000653 will have characterstics of FieldA(Key2) of Table B*/
I want to perform as below: Table A and B are memory tables already loaded in QVW.
Load *,NewField1, NewField2 resident TableA
left join([Table C])
Load FieldA, FieldB resident Table B; /* Join is based on Field1 and FieldA*/
I hope I have put in my query clearly. Kindly share how it can be implemented in QLIKVIEW.