Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: If then statement?

xoxo

Tim Lathrop

Fax: 1+ 832.251.8947

Mobile: 1+ 281.650.6901

www.Valprime.com

10 Replies

Re: If then statement?

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

Re: Re: If then statement?

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.

Re: If then statement?

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

Re: If then statement?

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

Re: If then statement?

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

Re: If then statement?

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

Re: If then statement?

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

Re: If then statement?

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

Re: Re: If then statement?

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.