Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have three tables.
I want to add the appropriate published price to the trade data table when I select the specific published date. When I select the appropriate published date in Qlikview the system populates the most current price. I want the published price to be tied to the range begin date.
Something like this:
If Range begin is < or = published price date then use range date as published price date and return published price for that date.
xoxo
Tim Lathrop
Fax: 1+ 832.251.8947
Mobile: 1+ 281.650.6901
www.Valprime.com
You're going to need to use interval match in the load script. Something along these lines:
http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/25/creating-intervals-from-change-dates
EDIT: I originally posted the wrong article. This is what you should be looking for:
I'm sorry. I am a newbie. Assuming I am able to get the interval date correct, how would I query the correct price in my published price table? I have attached a sample of the table and what I want to return.
The interval match will link things properly in the QV data model so that the correct price is linked to the correct date.
If you need further help, please give an example of Trade Data to go along with the Published Prices data that was in your spreadsheet.
Second tab on excel file is what I’m getting in Qlikview table. Tab 3 is what I’m looking for. This is a single trade.
Tim Lathrop
Fax: 1+ 832.251.8947
Mobile: 1+ 281.650.6901
www.Valprime.com
I need raw data in order to tell you how to fix it. What you're getting in the QV table needs to be changed...
Trading info is on third tab.
Thank you so much for looking at this.
Tim Lathrop
Fax: 1+ 832.251.8947
Mobile: 1+ 281.650.6901
www.Valprime.com
Is Location supposed to be part of the key between the two tables? Because I'm noticing that the values in the Location fields don't match up for the data you gave me.
No. I have a location table with a location code that maps the location to the appropriate curve. In excel I concatenated the curve ID with the date an performed a vlookup for the appropriate price for the appropriate date.
Tim Lathrop
Fax: 1+ 832.251.8947
Mobile: 1+ 281.650.6901
www.Valprime.com
For the spreadsheet you gave me, this should do the trick:
Trading:
LOAD [Curve Date],
Portfolio,
[Deal Type],
[C-Party],
Trader,
Pipeline,
[Point Name],
Zone,
Location,
[Deal #],
[C-Party1],
[Buy/Sell],
Description,
Portfolio1,
[Trade Date],
RiskType,
[Risk Type],
Commodity,
Pipeline1,
Point,
[Range Beg],
[Range End],
[Deal Price],
Month,
[Profit Center],
[NG Volume],
Source,
PriceDescription
FROM TB01_20140319_122410.xls
(biff, embedded labels, table is [Trading Table$]);
PublishedPrices:
LOAD [Curve date],
Location as PricesLocation, //I had to rename this field because the Location fields (at least in the data that you gave me) don't match up
[Published Price]
FROM TB01_20140319_122410.xls
(biff, embedded labels, table is [Published prices$]);
intervalmatch([Curve date])
load [Range Beg], [Range End]
resident Trading;
Then you can just create a table like normal because everything is linked properly.
File is also attached.