topic Firstsortedvalue and stock levels in QlikView

Firstsortedvalue and stock levels

Thu, 29 Dec 2011 13:02:48 GMT

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

Firstsortedvalue and stock levels

swuehl — Thu, 29 Dec 2011 14:43:42 GMT

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?

Firstsortedvalue and stock levels

Thu, 29 Dec 2011 15:11:32 GMT

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

Firstsortedvalue and stock levels

swuehl — Thu, 29 Dec 2011 18:04:43 GMT

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