Latest value when choosing date

[Anonymous]

Hi,

Is there a way to show the last change of a value depending on what date you choose?

For example, i have an interest of 3,4% on June 6th, the next change to that interest was at July 10th. When I, in my application choose to look at June 30th

I want to see the interest 3,4% right now i don't see anything.

I have connected the change date of the interest with the calendar dates. And the calendar dates are generated.

Thanks in advance!

//A.

[gandalfgray]

Hi Annika

I would do something like in the attached qvw.

First make a (temporary)  table where you have the valid range for a specific rate.

Then connect the Rate for each day to the Calendar table using IntervalMatch.

hth/gg

[#3]

Hi Annika,

One approach you can use is to maintain a table of the interval of date and rate during that interval, like

FromDate, ToDate, Rate

21/01/2011, 21/02/2011, 2.1

21/02/2011, 21/03/2011, 3.4

21/03/2011, 21/04/2011, 3.7

21/04/2011, 21/05/2011, 1.7

..............,...............,.............

.............,...................,.......

then when you selected a date, have it compared which interval it lies in... and then select the corresponding rate in your calculation.

Hope this helps.

you can attach a sample application also in case any more help is needed

rgrds,

Abhinava

[Anonymous]

the problem is that the rate range will be dynamic depending on when the rates change. i get a file and in that file i get the "chagedate" so to speak. så the interval is going to be dynamic, how do i do this?

[gandalfgray]

Hi again!

My suggested solution allows dynamic intervals as you can see in the qvw

/gg

[Anonymous]

Ok. ringht now, i'm behind some firewall that keeps me from downloading the file unfortunately.

[gandalfgray]

Here's the load script with my suggested method:

Let vDay = today();

Calendar:

Load Date(today()-rowno()) As Date

AutoGenerate(60);

Interest:

LOAD * INLINE [

    Rate, DateEffective

    3.5, 2011-06-15

    4.0, 2011-07-07

    3.1, 2011-01-01

];

// Above are our "source"-data

// The approach is to connect the Rate to the Calendar table.

// To do that we:

// 1. Add a DateEffectiveTo to our original Interest table

// 2. Connects the Rate to the Calendar table using IntervalMatch using our temporary Interest table

// 1:

tmpInterest:

Load Rate As __Rate,

    DateEffective As __DateEffectiveFrom,

    Date(if(isnull(Previous(DateEffective)),today(),Previous(DateEffective)-1)) As __DateEffectiveTo

Resident Interest

Order by DateEffective Desc;

Drop table Interest;

// 2:

Left Join (Calendar)

    IntervalMatch (Date)

    Load __DateEffectiveFrom,__DateEffectiveTo

Resident tmpInterest;

Drop Field __DateEffectiveTo;

Left Join (Calendar)

Load Dual(__Rate,if(isnull(__DateEffectiveFrom),null(),__DateEffectiveFrom)) As Rate,

      __DateEffectiveFrom

Resident tmpInterest;

Drop Table tmpInterest;

[Anonymous]

thanks!

but i'm having som problems. my "interest" tabel consists of more that just Rate and DateEffective so later on where you drop the interest table all those fields diappear as well. an since it's a group by statment it can't be more than it can't be more than 2 fields. i'm lost.

[Anonymous]

if i don't drop the interest table the selektion of date deos not work at all. everything blanks out when choosing a date.

[gandalfgray]

Here is a modified version.

I have changed the logic a bit (keeps a separate Calendar table) and added a couple of example fields.

Let vDay = today();

Calendar:

Load Date(today()-rowno()) As Date

AutoGenerate(120);

sourceInterest:

LOAD * INLINE [

    Rate, DateEffective, MoreFields, EvenMoreFields

    3.5, 2011-06-15, Hi, 998

    4.0, 2011-07-07, Hello, 5665

    3.1, 2011-01-01, Bye, 6445

];

// Above are our "source"-data

// The approach is to connect the Rate to the Calendar table.

// To do that we:

// 1. Add a DateEffectiveTo to our original Interest table

// 2. Connects the Rate to the Calendar table using IntervalMatch using our new Interest table

// 1:

Interest:

Load Rate,

    DateEffective As DateEffectiveFrom,

    Date(if(isnull(Previous(DateEffective)),today(),Previous(DateEffective)-1)) As DateEffectiveTo,

    MoreFields,

    EvenMoreFields  // If you have more fields just add them here

Resident sourceInterest

Order by DateEffective Desc;

Drop table sourceInterest;

// 2:

Left Join (Calendar)

    IntervalMatch (Date)

    Load DateEffectiveFrom,DateEffectiveTo

Resident Interest;

Drop Field DateEffectiveTo; // Drop this to avoid syntetic keys