I have a question where I couldn't find an answer by googling around. I'm creating a system where two tables are matched based on Symbol as a key. I summarize it in sample data (The values are not relevant):
Table 1 - Represents data aggregation from Stock closing prices
Symbol
table1.Date
Stock closing price in local currency
Stock closing price in USD
XY
2016-11-01
120.50
12.4
XY
2016-10-31
121.10
12.8
XY
2016-10-30
119.20
12.1
CB
2016-11-01
11221
102.2
...
...
...
...
Table 2 - Represents stock purchasing actions
Symbol
table2.Date
Stock purchase price in local currency
Stock purchase price in USD
XY
2016-11-01
119.50
12.15
CB
2016-11-01
11220.5
102.18
...
...
...
...
Table 2 has way less dates than Table 1.
When I combine this together in a QlikSense view and the data is mapped on Symbol the data looks like this:
Combined Table:
Symbol
Date
Stock purchase price in local currency
Stock purchase price in USD
Stock closing price in local currency
Stock closing price in USD
XY
2016-11-01
119.50
12.15
-
-
CB
2016-11-01
11220.5
102.18
-
-
...
...
...
...
-
-
The values in the "Stock closing price in local currency" and the "Stock closing price in USD" columns are empty. It is because the table1.Date is not aligned with table2.Date.
Ideal Table:
Symbol
Date
Stock purchase price in local currency
Stock purchase price in USD
Stock closing price in local currency
Stock closing price in USD
XY
2016-11-01
119.50
12.15
120.50
12.4
CB
2016-11-01
11220.5
102.18
11221
102.2
...
...
...
...
...
...
If I make a filter for table2.Date for a specific date, then I will see the missing data, but that's quite a workaround. It must be a way to match the Stock based on symbol and then find the correct Date value from Table 1 to add it to the Combined table.
Is there a way to map these together that for every stock purchase decision it shows the information from the other table with the stock closing prices?
