Date matching issue

Tue, 01 Nov 2016 13:39:24 GMT

Heyho Everyone!

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?

I tried to create it with Set Analysis Wizzard for a formula (http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=) but couldn't find the right one where the system would give me back the corresponding value.

It would be a great help.

Have a great day,

Gabor

topic Date matching issue in QlikView

Date matching issue