Firstsortedvalue and stock levels

[#1]

Hi,

I have a table with stock levels like:

ID,      Date,      Quantity

1, '01/12/2011', 11

1, '04/12/2011', 22

1, '08/12/2011', 55

1, '10/12/2011', 33

and a calendar table associated by Date field.

I would like to display the stock levels for every possible day in a given date range. I was trying to use the FirstSortedValue() function, but without any luck so far. I'm stuck without any clue how to solve this.

I would like to avoid filling the missing data in the load script.

Anyone can help?

Thanks, Przemek

[swuehl]

When you are saying 'for every possible day in a given date range', how do you create the date range? Do you work with a master calendar?

One way to solve it would be to create FromDate and ToDate in the script giving the date range that the quantity is valid, and then use IntervalMatch together with a master calendar.

If you don't want to use a master calendar (which I would suggest to use anyway), could you describe how you want to display / create the date range?

[#3]

I do have a master calendar.

I was thinking about using IntervalMatch, but that would give me a lot of data redundancy and would probably influence performance.

I could use seperate calendar to create the date range. I am not sure if that's the right approach and how should the expressions look like in such case.

Many thanks, Przemek

[swuehl]

Przemek,

if you want to keep the calendar and the Quantity tables separated you could use something like this:

LET vDateMin = Num(MakeDate(2011,1,1)); 

LET vDateMax = Floor(MonthEnd(Today())); 

LET vDateToday = Num(Today()); 

Calendar: 

LOAD

Date($(vDateMin) + RowNo() - 1) AS CalendarDate 

AUTOGENERATE 1 

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

Data:

LOAD * INLINE [

ID,      Date,      Quantity

1, '01/12/2011', 11

1, '04/12/2011', 22

1, '08/12/2011', 55

1, '10/12/2011', 33

];

Data2:

LOAD ID, Date as DateFrom, alt(previous(Date)-1,$(vDateMax)) as DateTo, Quantity resident Data order by Date desc;

drop table Data;

And then a simple chart with dimension CalendarDate and as expression:

=sum(if(CalendarDate<=DateTo and CalendarDate >= DateFrom, Quantity))

This will probably be not very performant and I would personally go for doing it in the script.

You could do it with an intervalmatch or by filling in all missing values in your Quantity table.

Something similar is also just being discussed here:

http://community.qlik.com/message/174676

Regards,

Stefan