Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Managing data when there is no date

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.

7 Replies
sunny_talwar

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

Not applicable
Author

Hello!

I need the traceability of the data when there is no date(MONTH-YEAR).

Another 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 hope that you understand me!! thank you!!

Anonymous
Not applicable
Author

Is this kind of thing you are after ?

How to populate a sparsely populated field

MarcoWedel

Please describe how you achieved the results of your initial example to get a solution that also fits those input data.

thanks

regards

Marco

Not applicable
Author

Hello Marco!
The difference between the first example and the latter example is that the first example is simpler but the process is the same . At least that's what I tried.

I need something like what makes this link: How to populate a sparsely populated field but I need more fields not only the Rate.

I'm working in it

Not applicable
Author

Thank you. It is quite useful

Not applicable
Author

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.