topic Re: Managing data when there is no date in QlikView

Managing data when there is no date

Fri, 18 Dec 2015 20:30:03 GMT

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.

Re: Managing data when there is no date

sunny_talwar — Fri, 18 Dec 2015 21:01:19 GMT

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

Sat, 19 Dec 2015 09:59:54 GMT

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!!

Re: Managing data when there is no date

Anonymous — Sat, 19 Dec 2015 10:03:18 GMT

Is this kind of thing you are after ?

How to populate a sparsely populated field

Re: Managing data when there is no date

MarcoWedel — Tue, 22 Dec 2015 23:20:55 GMT

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

thanks

regards

Marco

Re: Managing data when there is no date

Wed, 23 Dec 2015 08:46:18 GMT

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

Re: Managing data when there is no date

Wed, 23 Dec 2015 08:46:58 GMT

Thank you. It is quite useful

Re: Managing data when there is no date

Wed, 23 Dec 2015 16:04:46 GMT

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.