Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
ID | Year | Field | Res | Res2 | Month | Date |
21 | 2008 | State | SA | AP | 6 | 05/06/2008 14:11 |
21 | 2008 | State | AP | AAP | 2 | 29/02/2009 13:19:51 |
21 | 2008 | State | AAP | PL | 2 | 25/02/2009 9:13 |
22 | 2011 | Language | B2 | B1 | 11 | 12/11/2011 9:00 |
22 | 2011 | State | SA | AP | 11 | 12/11/2011 10:00 |
22 | 2012 | Language | C1 | C2 | 1 | 01/01/2012 8:00 |
And the result dataset:
ID | Year | Field | Res | Res2 | Month | Date |
21 | 2008 | State | SA | AP | 6 | 05/06/2008 14:11 |
21 | 2008 | State | SA | AP | 7 | 05/06/2008 14:11 |
21 | 2008 | State | SA | AP | 8 | 05/06/2008 14:11 |
21 | 2008 | State | SA | AP | 9 | 05/06/2008 14:11 |
21 | 2008 | State | SA | AP | 10 | 05/06/2008 14:11 |
21 | 2008 | State | SA | AP | 11 | 05/06/2008 14:11 |
21 | 2008 | State | SA | AP | 12 | -' |
21 | 2008 | State | SA | AP | 1 | -' |
21 | 2008 | State | AP | AAP | 2 | 29/02/2009 13:19:51 |
21 | 2008 | State | AAP | PL | 2 | 25/02/2009 9:13 |
22 | 2011 | Language | B2 | B1 | 11 | 12/11/2011 9:00 |
22 | 2011 | State | SA | AP | 11 | 12/11/2011 10:00 |
22 | 2011 | Language | B2 | B1 | 12 | |
22 | 2011 | State | SA | AP | 12 | |
22 | 2012 | Language | C1 | C2 | 1 | 01/01/2012 8:00 |
22 | 2011 | State | SA | AP | 1 |
Thank you for your help.
I am having trouble understand how you are arriving at the output you have shared? What is the logic?
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!!
Is this kind of thing you are after ?
Please describe how you achieved the results of your initial example to get a solution that also fits those input data.
thanks
regards
Marco
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
Thank you. It is quite useful
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:
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.