Load field from joining table based on if condition satisfied from sub string value from left table

Mon, 02 Sep 2013 16:25:28 GMT

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

Re: Load field from joining table based on if condition satisfied from sub string value from left table

Tue, 03 Sep 2013 04:49:37 GMT

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

topic Load field from joining table based on if condition satisfied from sub string value from left table in QlikView

Load field from joining table based on if condition satisfied from sub string value from left table

Re: Load field from joining table based on if condition satisfied from sub string value from left table