topic Re: Join two tables in QlikView

Join two tables

Tue, 10 Sep 2013 13:33:05 GMT

I have two tables like the following the common field for both the table is Currency_Code from Rate table and Currency from Fact table.

when I join together using a syntactic key i can see may duplicate data.

Requirement is as I need a table that should match the same currency in the both tables and the also need to get the Ex_Rate on if the invoice date is = Date in Rate table.

please give a idea....

Rate:

Date,

Currency_Code,

Ex_Rate

From Table1;

Fact:

Id,

Location,

Invoice_Number,

Invoice_Date,

Weight,

Currency,

Amount

from Table2;

Thanks and Regards

Chriss

Re: Join two tables

Oleg_Troyansky — Tue, 10 Sep 2013 13:52:03 GMT

Since the Currency rate is driven by two fields - Currency Code and Date, you have 2 choices:

1. Keep two separated tables, linked by 2 fields, which causes the synthetic key. You may chose to replace the synthetic key with a combo key that's generated manually.

2. Join the Currency Rate into the Fact, based on the two join fields (the Currency code and the date). I'd recommend this approach, because it is likely to perform better:

Fact:

LOAD

...

;

left join (Fact) load

Date as Invoice_Date,

Currency_Code as Currency,

Ex_Rate

From Table1;

best,

Oleg Troyansky

www.masterssummit.com

Re: Join two tables

Tue, 10 Sep 2013 14:14:42 GMT

Hi Oleg,

Awesome, its works like a gem... as I would like to more on this how do i learn this where I can get this stuff, please share your idea.

Thanks and Regards

Chriss

Re: Join two tables

Oleg_Troyansky — Tue, 10 Sep 2013 14:52:29 GMT

Chriss,

this is part of advanced Data Modeling. You can learn some basic ideas in the standard QlikView education curriculum (Developer Class), a bit more in the "Advanced Topics in Design and Development".

You can read about data modeling in Barry Harmsen's book "QlikView 11 for Developers".

Beyond that, the only advanced technical venue that I'm aware of is the Masters Summit for QlikView, where Barry Harmsen is teaching an excellent 4-hours Data Modeling class.

best,

Oleg Troyansky

www.masterssummit.com

Re: Join two tables

Wed, 11 Sep 2013 14:19:22 GMT

Hi Oleg,

Thanks for your valuable information, let me try that book "QlikView 11 for Developers". The solution what your given is working fine but still I am missing one thing.

The requirement is need to convert the Amount with a Ex_Rate when the Currency is selected, the twist is when I select the 'EUR' currency it shows the invoices who's Currency owe's the EUR the same for all the currency but what I want when I select the 'EUR' currency all the invoices based on their currency the Amount / Ex_Rate.

As I need this in a script, please give some clue to proceed further.

Thanks and Regards

Chriss

Re: Join two tables

Oleg_Troyansky — Wed, 11 Sep 2013 14:48:31 GMT

Well, this is a different requirement and a different solution...

First, you want to convert all the original transactions to a common currency, for example USD or EUR.

Then, you need to calculated all the transactions in the desired currencies for the presentation. You can either pre-calculate multiple amounts in the script or keep the rates in a table and calculate it on the fly.