Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Problem with date

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!

1 Solution

Accepted Solutions

Re: Problem with date

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;

Capture.PNG

4 Replies

Re: Problem with date

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;

Capture.PNG

Re: Problem with date

Not applicable

Re: Problem with date

Hi! I know that information. Thanks!

Not applicable

Re: Problem with date

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.

Community Browser