topic Re: Combining two tables with calculated values in QlikView

Combining two tables with calculated values

Thu, 26 Sep 2013 17:17:23 GMT

Hi,

I have two tables, one with sales values and currencies (BLUE table), and one currency and exchange rate table (BLACK table). How can I combine these so that I can see the exchange rate and (Sales Price * Currency) (RED table).

I have searched the forum, and I am convinced that the answer is there. I have tried several suggestions, but I cannot get lookup or calculated fields to work at all. Any good suggestions to how this load script would look like?

Regards

Thor

Re: Combining two tables with calculated values

srchilukoori — Thu, 26 Sep 2013 17:25:52 GMT

Load the two tables into qlikview, they are combined automatically, due to the common field name "Currency".

Re: Re: Combining two tables with calculated values

srchilukoori — Thu, 26 Sep 2013 17:32:26 GMT

check the attached file

Re: Combining two tables with calculated values

rodrigo_damas — Thu, 26 Sep 2013 17:59:59 GMT

[Blue_Table]:

LOAD [CURRENCY],

[SALES PRICE]

FROM Blue_Table_Path;

Left Join

LOAD [CURRENCY],

[EXCHRATE]

FROM Black_Table_Path;

[Red_Table]:

LOAD [CURRENCY],

[SALES PRICE]

[EXCHRATE],

[SALES PRICE] * [EXCHRATE] as [SEK AMOUNT]

Resident [Blue_Table];

Drop Table [Blue_Table];

Re: Combining two tables with calculated values

v_iyyappan — Thu, 26 Sep 2013 19:15:27 GMT

Hi,

In qlikview automatically join the two table based on common field name.

Table1:

LOAD [CURRENCY],

[SALES PRICE]

FROM Blue_Table_Path;

Tabl2:

LOAD [CURRENCY],

[EXCHRATE]

FROM Black_Table_Path;

Use the Straight table and expression expression like Sum([SALES PRICE] * [EXCHRATE]).

Regards,

Re: Combining two tables with calculated values

Anonymous — Thu, 26 Sep 2013 19:36:47 GMT

Mr.srchilukoori is right.

Take a chance on it.

Good Luck.

Re: Combining two tables with calculated values

struniger — Fri, 27 Sep 2013 06:44:03 GMT

Extending from Rodrigos' answer you could also load the FX rates as a map to avoid a JOIN.

(Have a look here for some background: http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap)

mapFX:

MAPPING LOAD

[CURRENCY],

[EXCHRATE]

FROM Black_Table_Path;

[Red_Table]:

LOAD [CURRENCY],

[SALES PRICE]

[EXCHRATE],

[SALES PRICE] * [EXCHRATE] as [SEK AMOUNT]

;

LOAD [CURRENCY],

[SALES PRICE],

applymap('mapFX',[CURRENCY]) AS [EXCHRATE]

FROM Blue_Table_Path

;

Good luck

Stefan

Re: Combining two tables with calculated values

Mon, 30 Sep 2013 09:46:45 GMT

Thanks. A lot of good tips. Worked perfectly.

/Thor