topic Re: How to populate date ranges in QlikView

How to populate date ranges

summerrain — Wed, 10 Feb 2016 17:24:28 GMT

I am new to QlikView and bumped into a problem to which I can not find a solution. I am loading the table below:

And before storing it to qvd, I want to populate DATE field with YYYYMM values wich are betweeen current dates. Desired result should look like this:

Does anyone know how should I aproach this problem. Any hint is apreciated!

Re: How to populate date ranges

marcus_sommer — Wed, 10 Feb 2016 17:42:02 GMT

Have a look here: Generating Missing Data In QlikView

- Marcus

Re: How to populate date ranges

sunny_talwar — Wed, 10 Feb 2016 17:54:35 GMT

Try this may be:

Table:

LOAD EmpID,

Date(Date#(Date, 'YYYYMM'), 'YYYYMM') as Date,

DepID,

Dep_Desc

Inline [

EmpID, Date, DepID, Dep_Desc

1, 201504, A1, MainA

1, 201508, A1, MainA

1, 201511, A1, A Main

];

Join(Table)

LOAD EmpID,

Date(AddMonths(MinDate, IterNo()), 'YYYYMM') as Date

While AddMonths(MinDate, IterNo()) <= YearEnd(MinDate);

LOAD EmpID,

Min(Date) as MinDate

Resident Table

Group By EmpID;

FinalTable:

NoConcatenate

LOAD EmpID,

Date,

If(IsNull(DepID), Peek('DepID'), DepID) as DepID,

If(IsNull(Dep_Desc), Peek('Dep_Desc'), Dep_Desc) as Dep_Desc

Resident Table

Order By Date;

DROP Table Table;

Re: How to populate date ranges

summerrain — Wed, 10 Feb 2016 21:08:14 GMT

Thank you for the link Marcus and thank you for the code Sunny T. I will still need to figure out how to make it work when Date field can represent different year, but what you gave me is really helpful.