4 Replies Latest reply: Jan 4, 2016 5:43 AM by Roger Grossi RSS

    Problem with date

    Roger Grossi

      Hello!

      I need the traceability of the data when there is no date(MONTH-YEAR).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.

       

       

      An example:

       

      table A:

      ID-MONTH-YEAR-FIELD1-FIELD2
      002-2-2005-10-B

      002-5-2005-40-D

      023-11-2008-25-C

      023-2-2009-30-H

       

      ID 002, the last date is 5-2005 and the first date is 2-2005. I have no data in 3-2005 and 4-2005 but in these months there is no changes, the data should be the same than in 2-2005.

       

       

      I need this result:

      table A:

      ID-MONTH-YEAR-FIELD1-FIELD2

      002-2-2005-10-B
      002-3-2005-10-B

      002-4-2005-10-B

      002-5-2005-40-D

      023-11-2008-25-C

      023-12-2008-25-C

      023-1-2009-25-C

      023-2-2009-30-H

       

       

      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 type 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.

       

      Thank you for your help!