7 Replies Latest reply: Dec 23, 2015 3:46 AM by Roger Grossi RSS

    Managing data when there is no date

    Roger Grossi

      Hello,

      I want to fill in every data from the first to the last one. Since there are some data unavailable, I want to fill the blanks with the previous data.

      For example,

      I have data in the month 2 an the month 5, but i have no data of the months 3 and 4. I want the data of the months 3 and 4 to be the same as in the previous month with data available, which is the month 2.

       

      A detailed example.I have this dataset:

        

      IDYearFieldResRes2MonthDate
      212008StateSAAP605/06/2008 14:11
      212008StateAPAAP229/02/2009  13:19:51
      212008StateAAPPL225/02/2009 9:13
      222011LanguageB2B11112/11/2011 9:00
      222011StateSAAP1112/11/2011 10:00
      222012LanguageC1C2101/01/2012 8:00

       

       

      And the result dataset:

        

      IDYearFieldResRes2MonthDate
      212008StateSAAP605/06/2008 14:11
      212008StateSAAP705/06/2008 14:11
      212008StateSAAP805/06/2008 14:11
      212008StateSAAP905/06/2008 14:11
      212008StateSAAP1005/06/2008 14:11
      212008StateSAAP1105/06/2008 14:11
      212008StateSAAP12-'
      212008StateSAAP1-'
      212008StateAPAAP229/02/2009  13:19:51
      212008StateAAPPL225/02/2009 9:13
      222011LanguageB2B11112/11/2011 9:00
      222011StateSAAP1112/11/2011 10:00
      222011LanguageB2B112
      222011StateSAAP12
      222012LanguageC1C2101/01/2012 8:00
      222011StateSAAP1

       

      Thank you for your help.

        • Re: Managing data when there is no date
          Sunny Talwar

          I am having trouble understand how you are arriving at the output you have shared? What is the logic?

          • Re: Managing data when there is no date
            Roger Grossi

            Hello,

            I found an example that is very interesting but I need some modifications:

            //Leo datos

            Directory;

            Datos:

            LOAD Producto,

                 date(Fecha) as Fecha,

                 Cant

            FROM

            Crono.xls

            (biff, embedded labels, table is Hoja1$);

             

             

            //Busco fecha menor y mayor

            FechaIni:

            LOAD rangemin(Fecha) as FechaIni

            resident Datos

            order by Fecha Asc;

             

             

            FechaFin:

            LOAD rangemin(Fecha) as FechaMax

            resident Datos

            order by Fecha desc;

             

             

            //Genero todas las fechas desde el inicio (Fecha menor) hasta la última fecha de los datos (Fecha mayor)

            AutoFecha:

            Load

              fieldValue('FechaIni', 1) + Iterno() - 1 as FechaTot

            Autogenerate 1

            while fieldValue('FechaIni', 1)+Iterno() <= fieldValue('FechaMax', 1);

             

             

            //Agrego fechas a datos

            right join(Datos)

            load FechaTot as Fecha resident AutoFecha;

             

             

            drop table FechaIni;

            drop table FechaFin;

             

             

            //Busco productos para generarles todas las fechas

            AutoProdu:

            Load distinct Producto as ProductoTot resident Datos;

             

             

            join (AutoFecha)

            LOAD ProductoTot resident AutoProdu;

             

             

            drop table AutoProdu;

             

             

            //Agrego productos y fechas

            join (AutoFecha)

            LOAD Producto as ProductoTot,

              Fecha as FechaTot,

              Cant

            resident Datos;

             

             

            drop table Datos;

             

             

            //Genero Producto por día con su respectiva cantidad.

            DatosFin:

            LOAD ProductoTot as ProductoFin,

              FechaTot as FechaFin,

              Cant as CantFin,

              if(`ProductoTot`=previous(`ProductoTot`),if(isNull(Cant),peek('Saldo'),Cant),Cant) as Saldo

            resident AutoFecha

            order by ProductoTot, FechaTot;

             

             

            drop table AutoFecha;

             

            The result:

            Capture.JPG

            I have the following problem: I have a lot of ProductoFin, not only ProductoA and ProductoB.

             

            I just need the start and end dates for each of the products. If product A starting on day 1 and ended on the 10th , the date must be between 1 and 10. If product B started on Day 2 and finished the day only 15 dates there must be enclosed 2 and 15 .
            Now it appears from the highest to the lowest date either product A or product B.