Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

soton34
New Contributor III

Selecting data by date

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:

BatchLocationDistanceDate
1A035901/07/11
2B055701/07/11
3A099801/07/11
1B056701/09/11
1D0341501/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

Tags (3)
4 Replies
chematos
Valued Contributor II

Re: Selecting data by date

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
New Contributor III

Re: Selecting data by date

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
Valued Contributor II

Re: Selecting data by date

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
New Contributor III

Re: Selecting data by date

Thanks for your help Jose

I have now managed to sort this through another discussion:

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

Sam

Community Browser