Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

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

AccountTickerMarket ValueTrade Date
ClipperABC406 Jan 2015
ClipperDEF506 Jan 2015

Blender

GHI208 Jan 2015
BlenderJKL309 Jan 2015
LindtABC1007 Jan 2015
LindtMNO508 Jan 2015

NAVTable

NAV DateClipperBlenderLindt
5 Jan 2015100020201500
6 Jan 2015101020251500
7 Jan 2015101525001505
8 Jan 2015102025101510
9 Jan 2015101025051490

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);


1 Solution

Accepted Solutions
Not applicable

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

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!

4 Replies
brian_booden
Contributor

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

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

Not applicable

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

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

AccountTrade DateMarket ValueTicker
Clipper6 Jan 201540ABC
Clipper6 Jan 201550DEF


NAVTable

AccountNAV DateNAV
Clipper5 Jan 20151000
Blender5 Jan 20152020
Lindt5 Jan 20151500
Clipper6 Jan 20151010
Blender6 Jan 20152025
Lindt6 Jan 20151500

Regards, Eveline

Not applicable

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

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!!

Not applicable

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

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!

Community Browser