Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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!
Try something like this:
Table:
LOAD *,
MonthName(MakeDate(YEAR, MONTH)) as MONTHYEAR
Inline [
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
];
Join (Table)
LOAD ID,
MonthName(AddMonths(Min, IterNo())) as MONTHYEAR,
Year(AddMonths(Min, IterNo())) as YEAR,
Num(Month(AddMonths(Min, IterNo()))) as MONTH
While AddMonths(Min, IterNo()) <= Max;
LOAD ID,
Max(MONTHYEAR) as Max,
Min(MONTHYEAR) as Min
Resident Table
Group By ID;
FinalTable:
NoConcatenate
LOAD ID,
MONTH,
YEAR,
MONTHYEAR,
If(Len(Trim(FIELD1)) = 0, IF(ID = Previous(ID), Peek('FIELD1')), FIELD1) as FIELD1,
If(Len(Trim(FIELD2)) = 0, IF(ID = Previous(ID), Peek('FIELD2')), FIELD2) as FIELD2
Resident Table
Order By ID, MONTHYEAR;
DROP Table Table;
Try something like this:
Table:
LOAD *,
MonthName(MakeDate(YEAR, MONTH)) as MONTHYEAR
Inline [
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
];
Join (Table)
LOAD ID,
MonthName(AddMonths(Min, IterNo())) as MONTHYEAR,
Year(AddMonths(Min, IterNo())) as YEAR,
Num(Month(AddMonths(Min, IterNo()))) as MONTH
While AddMonths(Min, IterNo()) <= Max;
LOAD ID,
Max(MONTHYEAR) as Max,
Min(MONTHYEAR) as Min
Resident Table
Group By ID;
FinalTable:
NoConcatenate
LOAD ID,
MONTH,
YEAR,
MONTHYEAR,
If(Len(Trim(FIELD1)) = 0, IF(ID = Previous(ID), Peek('FIELD1')), FIELD1) as FIELD1,
If(Len(Trim(FIELD2)) = 0, IF(ID = Previous(ID), Peek('FIELD2')), FIELD2) as FIELD2
Resident Table
Order By ID, MONTHYEAR;
DROP Table Table;
Hi,
maybe also helpful:
Generating Missing Data In QlikView
How to populate a sparsely populated field
regards
Marco
Hi! I know that information. Thanks!
Hi, I 'm testing with this code and I fuciona well although I have to make some changes obviously. Thank you very much.
The next step is to link a master calendar.