5 Replies Latest reply: Jun 7, 2017 3:10 AM by Sergey Shuklin RSS

    Previous Value

    Michele Pierobon

      Hi Qlikers,

      i need to know how can i obtain this result:

      I have a table with ITEM, MONTHS, MOV DATE, INVENTORY DATE and MIN MOV DATE.

      There are several Items that have some movement dates; i've taken the maximum movement date per month.

      PROBLEM:

      I need to calculate a date that is the INVENTORY DATE if minimum movement date is higher than the inventory date.

      It's the MIN MOV DATE if the minimum movement date is equal to the mov date and

      it's the last minimum MOV DATE when min mov date is lower then the mov date.

       

      I need to solve it on the script.

       

      Maybe everything is more clear in the picture.

      Thanks in advance.

        • Re: Previous Value
          Fer Fer

          Hi,

          from script:

          LOAD *, IF([MIN MOV DATE]<=[MOV DATE],[MOV DATE],[INVENTORY DATE]) AS CALCU;
          LOAD ITEM,
          MONTHS,
          [MOV DATE],
          [INVENTORY DATE],
          [MIN MOV DATE]
          FROM
          [..\..\..\Documents\Downloads\B.xlsx]
          (
          ooxml, embedded labels, table is Hoja1);

            • Re: Previous Value
              Michele Pierobon

              Thank you Fer for your answer,

              but the MIN MOV DATE is a field with only one date for item.

              MIN MOV DATE is the lowest mov date about the item and it's everytime lower or equal than the MOV DATE.

              I will have only MOV DATE with this condition (IF([MIN MOV DATE]<=[MOV DATE],[MOV DATE]).

              Any suggestions?

                • Re: Previous Value
                  Fer Fer

                  Hi Michelle

                  This can work for you

                   

                  tmp:
                  LOAD ITEM,
                  MONTHS,
                  [MOV DATE],
                  [INVENTORY DATE]

                  FROM
                  [..\..\..\Documents\Downloads\B.xlsx]
                  (
                  ooxml, embedded labels, table is Hoja1);

                  minDt:
                  LOAD min([MOV DATE]) AS MinDate  Resident tmp;

                  LET v_MinDare  = date(floor(peek('MinDate')),'DD/MM/YYYY');

                  SUMARY:
                  LOAD *, '$(v_MinDare)' AS [MIN MOV DATE], IF('$(v_MinDare)'<=[MOV DATE],[MOV DATE],[INVENTORY DATE]) AS CALCU Resident tmp;



                  DROP Table tmp,minDt;

              • Re: Previous Value
                Sergey Shuklin

                Hello, Michele!

                 

                In attached example you may find a front-end result. I'll try to solve the back-end task tomorrow!

                PFA.

                • Re: Previous Value
                  Sergey Shuklin

                  And here is the back-end solution (PFA).

                  For filling missing dates/values often can be usefull this script condition:

                  if(isnull(Field1),peek(Field1),Field1) as Field1

                  It takes previous value from Field1 if Field1 is null, and if end of the line is "as Field1" (e.g. the same field) so the condition will be cycled, otherwise it will take previous value just one time.

                   

                  You also can find informative this topic: Generating Missing Data In QlikView