Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
summerrain
New Contributor III

How to populate date ranges

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

cur_table.jpg

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:

desired_table.jpg

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

1 Solution

Accepted Solutions

Re: How to populate date ranges

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;


Capture.PNG

View solution in original post

3 Replies
MVP & Luminary
MVP & Luminary

Re: How to populate date ranges

Have a look here: Generating Missing Data In QlikView

- Marcus

Re: How to populate date ranges

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;


Capture.PNG

View solution in original post

summerrain
New Contributor III

Re: How to populate date ranges

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.