Combining two tables with calculated values

[#1]

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

[srchilukoori]

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

[srchilukoori · Accepted solution]

check the attached file

[rodrigo_damas]

[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];

[v_iyyappan]

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,

[Anonymous]

Mr.srchilukoori is right.

Take a chance on it.

Good Luck.

[struniger]

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

[#8]

Thanks.  A lot of good tips.  Worked perfectly.

/Thor