9 Replies Latest reply: Sep 25, 2018 4:43 AM by Przemyslaw Rybacki RSS

    Missing data gaps

    Przemyslaw Rybacki

      Hello All,

       

      I have fallowing problem.

      I need to fill missing data in the gaps, please check picture:

      Capture.PNG

      In my case before 1-2-2018 stock level needs to be 0 and after 1-2-2018 must be 19 until another stock level change.

       

      I got this script, but it look that peek() function is not fetching 19 from 1-2-2018.

       

      Stock:

      LOAD

          SHORTL62,

          %REPORT_DATE,

          X08BAL

      FROM [lib://2_Temp/BALANCE_TEMP_2.qvd]

      (qvd);

       

       

      final:

      NoConcatenate

      load

          SHORTL62,

          %REPORT_DATE,

          if(SHORTL62=Previous(SHORTL62),if(len(trim(X08BAL))=0,peek(X08BAL),X08BAL),0) as [Stock level]

      resident Stock

      order by SHORTL62,%REPORT_DATE;

       

      drop table Stock;

       

      Could you advise what needs to be done?

        • Re: Missing data gaps
          Petter Skjolden

          Peek function needs to have the field name in single quotation marks:

           

          Peek('X08BAL')

            • Re: Missing data gaps
              Przemyslaw Rybacki

              Thank you for answer!

               

              I changed peek function as you mentioned, but is the same result.

               

              Stock:

              LOAD

                  SHORTL62,

                  %REPORT_DATE,

                  X08BAL

              FROM [lib://2_Temp/BALANCE_TEMP_2.qvd]

              (qvd);

               

               

              final:

              NoConcatenate

              load

                  SHORTL62,

                  %REPORT_DATE,

                  if(SHORTL62=Previous(SHORTL62),if(len(trim(X08BAL))=0,peek('X08BAL'),X08BAL),0) as [Stock level]

              resident Stock

              order by SHORTL62,%REPORT_DATE;

               

               

              drop table Stock;

                • Re: Missing data gaps
                  Petter Skjolden

                  Yes - the quotation marks can be used but are not necessary actually....

                   

                  I think your problem is that you have to tell which table you are peeking from when you don't include the field in the last table:

                   

                  Peek( X08BAL , RecNo()-1, 'Stock' )

                    • Re: Missing data gaps
                      Przemyslaw Rybacki

                      I still have the gaps.

                       

                      My Script:

                      Stock:

                      LOAD

                          SHORTL62,

                          %REPORT_DATE,

                          X08BAL

                      FROM [lib://2_Temp/BALANCE_TEMP_2.qvd]

                      (qvd);

                       

                      final:

                      NoConcatenate

                      load

                          SHORTL62,

                          %REPORT_DATE,

                          if(SHORTL62=Previous(SHORTL62),if(len(trim(X08BAL))=0,peek('X08BAL',RecNo()-1,'Stock'),X08BAL),0) as [Stock level]

                      resident Stock

                      order by SHORTL62,%REPORT_DATE;

                       

                      drop table Stock;

                      STORE final INTO 'LIB://2_Temp//BALANCE_Final.qvd' (qvd);

                        • Re: Missing data gaps
                          Petter Skjolden

                          Well ... this should work:

                           

                          final:
                          NoConcatenate
                          load
                              SHORTL62,
                              %REPORT_DATE,
                              if(SHORTL62=Previous(SHORTL62),if(len(trim(X08BAL))=0 or IsNull(X08BAL),peek('Stock level'),Peek(X08BAL,RecNo()-1,'Stock')),0) as [Stock level]
                          resident Stock
                          order by SHORTL62,%REPORT_DATE;
                          
                  • Re: Missing data gaps
                    Ruben Marin

                    Hi, in two steps, one to add the end date of the range (in this sample I used today()), and another step to create the extra records, value is only filled for first record but can be easily passed to other records.

                     

                    tmpLastDate:
                    LOAD
                    SHORTL62,
                    %REPORT_DATE,
                    If(SHORTL62=Peek(SHORTL62), Peek(%REPORT_DATE), Date(Today())) as lastDate, // fill from the last record until today
                    [Stock level]
                    resident tmpData
                    Order by SHORTL62, %REPORT_DATE desc
                    ;
                    
                    filledData:
                    NoConcatenate LOAD
                    SHORTL62,
                    %REPORT_DATE+IterNo()-1 as %REPORT_DATE,
                    If(IterNo()=1, [Stock level]) as [Stock level]
                    Resident tmpLastDate
                    While %REPORT_DATE + IterNo()-1 < lastDate;
                    
                    DROP Table tmpData;
                    DROP Table tmpLastDate;
                    
                    
                    
                    • Re: Missing data gaps
                      Przemyslaw Rybacki

                      Thank you both for help.

                       

                      The data is still missing. I have no idea what might be the problem.

                      I attached qvd with selected two products, maybe you can find error there.

                       

                      Thanks!