Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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

Tags (1)
1 Reply
Not applicable

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

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

Community Browser