Creating a new column out of columns from two tables (based on a date condition)

[#1]

Hi all, I'm trying to solve the case below, but I have trouble finding the answer. I really hope someone could help me solving it.

Each ticker is traded on a trade date, the traded amount is called Market Value.

For each traded ticker, I'm trying to create a new column Market Value (MV) divided by the Net Asset Value (NAV).

The NAV in this column should be the NAV (from NAVTable) that belongs to the account the ticker is in and the date Trade Date and NAV Date should be equal.

The tables below are the ones that I have.

TradeTable

Account	Ticker	Market Value	Trade Date
Clipper	ABC	40	6 Jan 2015
Clipper	DEF	50	6 Jan 2015
Blender	GHI	20	8 Jan 2015
Blender	JKL	30	9 Jan 2015
Lindt	ABC	100	7 Jan 2015
Lindt	MNO	50	8 Jan 2015

NAVTable

NAV Date	Clipper	Blender	Lindt
5 Jan 2015	1000	2020	1500
6 Jan 2015	1010	2025	1500
7 Jan 2015	1015	2500	1505
8 Jan 2015	1020	2510	1510
9 Jan 2015	1010	2505	1490

My answer should be something like an additional column in TradeTable called "MV/NAV, the answers being

3.96% (=40/1010)

4.95% (=50/1010)

0.80% (=20/2510)

1.20% (=30/2505)

6.64% (=100/1505)

3.31% (=50/1510)

Code that I have in my Data Load Editor so far:

TradeTable:
Load
    "Account"

    "Ticker"

    "Market Value",
    "Trade Date"

FROM [lib://Desktop/Sheet1.xlsx] (ooxml, embedded labels, table is Sheet1);

NAVTable:

CrossTable(Account,NAV,1)

Load
    "NAV Date",
    "Clipper",

    "Blender",

    "Lindt"

FROM [lib://Desktop/Sheet1.xlsx] (ooxml, embedded labels, table is Sheet2);

[brian_booden]

Try this in the Data Load Editor:

TradeTable:
Load
    "Account"

    "Ticker"

    "Market Value",
    "Trade Date"

FROM [lib://Desktop/Sheet1.xlsx] (ooxml, embedded labels, table is Sheet1);

NAVTable:

CrossTable(Account, NAV)

LOAD

    "NAV Date" as "Trade Date",

    Clipper,

    Blender,

    Lindt

FROM [lib://Desktop/Sheet1.xlsx] (ooxml, embedded labels, table is Sheet2);

The link between your 2 tables should then be Trade Date and Account.

Then, in your expression / measure on your chart, to create your calculation:

Sum([Market Value] / [NAV])

Set Number Formatting on the expression to Number, Simple and format 12.34%.

That should be it!

Regards,

Brian

[#3]

Hi Brian,

thanks for your answer.

It actually helps me getting the answer I need, but I'm still curious on how to solve the initial problem I wrote down. I figured that after applying the cross table, the data looks like the tables below (I left out a part of the data).

Do you think it would be possible to do a double join or a join on multiple columns, i.e., doing a double join where NAV from NAVTable is included into TradeTable when TradeTable.Account = NAVTable. Account and TradeTable.TradeDate = NAVTable.NAVDate?

TradeTable

Account	Trade Date	Market Value	Ticker
Clipper	6 Jan 2015	40	ABC
Clipper	6 Jan 2015	50	DEF

NAVTable

Account	NAV Date	NAV
Clipper	5 Jan 2015	1000
Blender	5 Jan 2015	2020
Lindt	5 Jan 2015	1500
Clipper	6 Jan 2015	1010
Blender	6 Jan 2015	2025
Lindt	6 Jan 2015	1500

Regards, Eveline

[#4]

Si gustas escribir en espaniol pregunta porque quieres usar un cross table ? otra cosa en que casos o cual seran las condiciones para usar los valores de cada columna de la tabla NAVTable?

te paso mi skype y mi email por su gustas te ayudo sirve que practico jejeej

limonbi29

olimon80@gmail.com

Saludos!!

[#5 · Accepted solution]

I found the solution.

The solution is changing NAV Date to Trade Date (as you already suggested).

Then a synthetic key is created as Account and Trade Date are in both tables, however, using a left join and dropping a table was enough to include the NAV into the TradeTable for the right Account and right Trade date (and to get rid of the synthetic key.

Left join (NAVTable)
load *
resident TradeTable;

drop table TradeTable;

Now all I have to do is find out how to divide the two, but I guess that will not be the biggest problem.

Thanks for thinking along!