Selecting data by date

[soton34]

Hi All

I have a table of data showing where products are stored, the distance and the date it was there.  If the stock is moved then the new location is recorded and the date of the movement.

This gives me a table similar to the one below:

Batch	Location	Distance	Date
1	A035	9	01/07/11
2	B055	7	01/07/11
3	A099	8	01/07/11
1	B056	7	01/09/11
1	D034	15	01/02/12

My problem is that I need to know, on any given date, where the stock was located (so that I can calculate the distance to the bin)

So how can I get a sale of batch 1 on 01/08/11 to return A035 and a sale of batch 1 on 01/01/12 to return B056?

Currently when I ask it for the Distance it either adds the 3 distances (9+7+15) or returns a Null.

Your assistance will be greatly appreciated as I am tasked to provide reports to measure and drive efficiency.

I look forward to your replies.

Sam

[chematos]

Hi

I think that you must create a Key field in your script with both fileds, Batch and Date so you will have a composite key and it will be unique for each combination.

Load *,

Batch&'_'&Date as %Key

FROM tableX;

Its always helpfull if you explain your script, your expressions in the charts or part of your model so we will be able to understand the problem much better.

Regards

[soton34]

Thanks Jose

I was trying to keep it simple so didn't include all the extra info.

It's all linked tables from Invoice History through batch movement history and the location history table above and a separate location distance table.

The main thrust of the question was how, if I only have two dates (a start date and and end date), can I make a date in the middle pick the right location.

I do not understand how creating a new field as a combination of batch and date will allow Qlikview to say where it was on a given date.

Is it set analysis?

[chematos]

Sorry but I haven´t undertood your question very well.

If I understand the problem now, you need to know if a date is between a start and end date, don´t you?

There are two ways to get that as I know, with set analysis or in load script with the function INTERVALMATCH() but I think that IntervalMatch didn´t work to this case

set analysis:

I don´t know how your expression looks like but this is an example with pseudocode

sum({<Date = {'>=$(=StartDate) <=$(=EndDate)'}>}Distance)

This is like Date between StartDate and EndDate

Date = {'>=$(=StartDate) <=$(=EndDate)'}

[soton34]

Thanks for your help Jose

I have now managed to sort this through another discussion:

http://community.qlik.com/message/240055#240055

Sam