Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

If then statement?

I have three tables.

  • Trade data
  • Published prices
  • Mapping table

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.

1 Solution

Accepted Solutions
Not applicable
Author

xoxo

Tim Lathrop

Fax: 1+ 832.251.8947

Mobile: 1+ 281.650.6901

www.Valprime.com

View solution in original post

10 Replies
Nicole-Smith

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:

IntervalMatch and Slowly Changing Dimensions

Not applicable
Author

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.

Nicole-Smith

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.

Not applicable
Author

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

Nicole-Smith

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

Not applicable
Author

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

Nicole-Smith

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.

Not applicable
Author

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

Nicole-Smith

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.