topic Re: Adding Calculated column and Outerjoin in QlikView

Adding Calculated column and Outerjoin

Thu, 23 Apr 2015 01:56:40 GMT

Requiremnet #1: Outer join with two different tables

I am using different reporting and working on replicating same data modeling using qlikview. above screen show join structure used to join tables. I have created one QVD for each table shown above and joined tables in Qlikview.

But there is issue in joining 'Movement History' with 'Underwriting' and 'Contract' table.

'Movement History table has Left join with 'Underwriting' and contract tables.

Left Join ( Contract)

Movement History:

and

Left Join ( Underwriting)

Movement History:

The movement we define outerjoin, system getting frozen and need to force restart it.

Requirement #2: Adding calculated column from two different tables.

I want to add calculated column which is derived from columns from the tables contract and Unit to group the records

Example:

If(unit.column='A' and Contract.columns='abc') Then

('Group1')

elseif If(unit.column='A' and Contract.columns <> 'abc') Then

('Group2')

elseif If(unit.column='B' and Contract.columns = 'yz') Then

('Group3')

Is it possible to add calculated dimension column from two different dimensions and use it in List Box for user selection.

Re: Adding Calculated column and Outerjoin

Oleg_Troyansky — Thu, 23 Apr 2015 15:24:48 GMT

Regarding your first requirement - I think you misunderstand the use of JOIN in QlikView... In order to recreate the structure presented in your document, you don't need to join these tables. Load them individually and let them link (not join) to each other, based on common field names. In QlikView, Joining means adding columns from one table into another.

Here is what happened in your script - you added the same fields from Movement History into Contracts and into Underwriting, and these multiple identical fields created a huge Synthetic key, causing the system to halt. Another possible cause is if you don't have any identical key fields - then QlikView performs a Cartesian Join, linking every row from one table to every row from another table.

The only issue in your data model is that Movement History, Underwriting and Contract are all linked between them. If all three tables are linked by the same single key, it might be OK. Otherwise, you may get a "Circular reference", and you'll have to resolve it.

As for your second requirement - you need to bring both fields into the same table in order to use both fields in the same calculation. You can achieve that using JOIN or using MAPPING (look them up in the manual, it's too long to describe in a single post).

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

Re: Adding Calculated column and Outerjoin

Fri, 07 Aug 2015 20:20:42 GMT

Thank you Oleg for the explanation it helped.