Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Create a new dimension

Is there a way to create a new dimension "End date" as following? whereby the end date will be the same project next phase start date minus 1 day??

Capture.PNG------->Capture1.PNG

4 Replies
sunny_talwar

Try this:

Table:

LOAD * Inline [

Project, Phase, StartDate

A,1,1/6/2016

A,2,1/8/2016

A,3,1/12/2017

B,1,1/5/2015

B,2,1/8/2016

B,3,1/7/2017

];

FinalTable:

LOAD *,

  Date(If(Project = Previous(Project), Previous(StartDate) - 1)) as EndDate

Resident Table

Order By Project, StartDate desc;

DROP Table Table;

undergrinder
Specialist II
Specialist II

Hi Lingling,

I would create a key that contains the current Phase+1, and left join the table for itself.

The formula will be for -1 day is just simple =date(StartDate-1).

eg.:

Base:

Load

     Project

     ,Phase

     ,Project & Phase+1 as Key

     ,StartDate

Resident/From [YourTable];

left join(Base)

Load

     Project & Phase

     ,StartDate

Resident Base;

G.

Not applicable
Author

Thanks, That is what exactly I want!

Not applicable
Author

Thank you for your help