topic Re: If then statement? in QlikView

If then statement?

Wed, 19 Mar 2014 15:43:55 GMT

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.

Re: If then statement?

Nicole-Smith — Wed, 19 Mar 2014 15:48:24 GMT

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

Re: Re: If then statement?

Wed, 19 Mar 2014 17:47:00 GMT

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?

Nicole-Smith — Wed, 19 Mar 2014 18:41:56 GMT

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.

Re: If then statement?

Wed, 19 Mar 2014 18:56:04 GMT

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?

Nicole-Smith — Wed, 19 Mar 2014 18:59:00 GMT

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

Re: If then statement?

Wed, 19 Mar 2014 19:13:28 GMT

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?

Nicole-Smith — Wed, 19 Mar 2014 19:22:59 GMT

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.

Re: If then statement?

Wed, 19 Mar 2014 19:34:28 GMT

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?

Nicole-Smith — Wed, 19 Mar 2014 19:40:19 GMT

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.

Re: If then statement?

Wed, 19 Mar 2014 19:54:16 GMT

xoxo

Tim Lathrop

Fax: 1+ 832.251.8947

Mobile: 1+ 281.650.6901