topic Re: Latest value when choosing date in QlikView

Latest value when choosing date

Anonymous — Mon, 08 Aug 2011 07:52:24 GMT

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.

Re: Latest value when choosing date

gandalfgray — Mon, 08 Aug 2011 08:54:48 GMT

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

Re: Latest value when choosing date

Mon, 08 Aug 2011 11:03:57 GMT

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

Latest value when choosing date

Anonymous — Wed, 17 Aug 2011 12:08:06 GMT

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?

Latest value when choosing date

gandalfgray — Wed, 17 Aug 2011 12:19:11 GMT

Hi again!

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

/gg

Latest value when choosing date

Anonymous — Fri, 19 Aug 2011 06:41:30 GMT

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

Re: Latest value when choosing date

gandalfgray — Fri, 19 Aug 2011 06:47:59 GMT

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;

Latest value when choosing date

Anonymous — Fri, 19 Aug 2011 10:50:55 GMT

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.

Latest value when choosing date

Anonymous — Fri, 19 Aug 2011 11:13:45 GMT

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

Re: Latest value when choosing date

gandalfgray — Fri, 19 Aug 2011 12:14:36 GMT

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

Re: Latest value when choosing date

Anonymous — Mon, 22 Aug 2011 07:08:41 GMT

thanks!

it looks better but i'm still having problems and i think it has to do with my source data. you see, i have one table that contains:

bank,

rate,

date (date where the interest changed)

and then i have another table that contains

ratename (the type of rate (3 month, 1 year etc. one bank can have several ratenames hence several rates with different dates)

i've attaced my file with my data and hoping you can help me.

Thanks in advance!

Latest value when choosing date

gandalfgray — Mon, 22 Aug 2011 10:42:06 GMT

Hi Annika

I have opened the attached qvw

What is the problem you are having?

/gg

Re: Latest value when choosing date

Anonymous — Mon, 22 Aug 2011 11:48:57 GMT

Ok. a few examples of "wrongs":

if you look at "Freedom Finance" in the field "name" they only have one date when it comes to "DateEffectiveFrom" that's ok, but. If you look at "Date" they don't have any. You should be able to choose all dates up until today. since there has not been a change to any of the timenames yet.

Enother example is if you choose name="Handelsbanken" and timename="1 år" you can see that the latest Date you can choose is 2011-07-03 but i should be today.

Re: Latest value when choosing date

gandalfgray — Tue, 23 Aug 2011 08:32:14 GMT

Hi Annika

I can't really say, what's wrong with your app, because I don't have your sourcedata.

But if you try my attached app where the sourcedata is like this:

sourceInterest:

LOAD * INLINE [

Rate, DateEffective, MoreFields, EvenMoreFields

3.5, 2011-06-15, SBAB, 998

3.9, 2011-04-01, Handelsbanken, 3mån

4.0, 2011-05-01, Handelsbanken, 1år

4.4, 2011-07-07, Handelsbanken, 1år

4.2, 2011-07-07, Handelsbanken, 3mån

3.1, 2011-01-01, Nordea, 6445

];

you can see that if you choose Handelsbanken + 1år + Rate 4.4 all the dates from 2011-07-07 up to 2011-08-23 is possible.

(btw: I can't reproduce your "Freedom Finance" problem in your app. iIf I choose Freedom Finance I have dates from 2011-07-06 and upto 2011-08-21 (which is as far as you have generated the calendar))