Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
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*/
end if
I want to perform as below: Table A and B are memory tables already loaded in QVW.
[Table C]:
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.
Thanks and Regards,
Kulbir
Hello Kulbir,
You need to name the fields that are used to join two tables with the same names. For example,
Field1 as FieldA
Once the join to performed you can use the resident command again and create the new field with an if() statement.
Another option would be to perform a mapping load of one of the tables and the applymap() function in the other table to use the TableB field in TableA.
Regards, Karl