4 Replies Latest reply: Jul 20, 2012 8:03 AM by Sam Sansome RSS

    Selecting data by date

    Sam Sansome

      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

        • Re: Selecting data by date
          Jose Tos

          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

            • Re: Selecting data by date
              Sam Sansome

              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?

                • Re: Selecting data by date
                  Jose Tos

                  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)'}