topic Re: Re: Generate Enddate by next StartDate in QlikView

Generate Enddate by next StartDate

Thu, 21 Aug 2014 14:32:04 GMT

I have a table where I have the StartDate for the each registration by each student-ID.

But I need to generate the Endate by using the next registrations startdate - 1 day. And for the latest registration the enddate should be set to Date(Today()) whenever the script is loaded.

Can anyone help me?

Attached a qvd file with sampledata. Rename/remowe the .xml from filename

Re: Generate Enddate by next StartDate

whiteline — Thu, 21 Aug 2014 15:05:45 GMT

Like this ?

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/25/creating-intervals-from-change-dates

Re: Generate Enddate by next StartDate

MarcoWedel — Thu, 21 Aug 2014 15:18:15 GMT

Hi,

one example:

matching currency exchange rate

tabExchangeRates:
LOAD * Inline [
date start, exchange rate
01/01/2014, 1.513
02/01/2014, 1.456
03/01/2014, 1.417
03/15/2014, 1.348
04/01/2014, 1.389
04/10/2014, 1.391
04/20/2014, 1.498
05/01/2014, 1.568
06/01/2014, 1.591
06/25/2014, 1.643
07/01/2014, 1.719
08/01/2014, 1.737
];

Left Join (tabExchangeRates)
LOAD *,
    AutoNumberHash128([date start], [date end]) as %PeriodID;
LOAD [date start],
    Alt(Date(Peek([date start])-1), Date(Today())) as [date end]
Resident tabExchangeRates
Order By [date start] desc;

hope this helps

regards

Marco

Re: Re: Generate Enddate by next StartDate

MarcoWedel — Thu, 21 Aug 2014 19:06:43 GMT

or using your sample data:

tabTmp:
LOAD Distinct
  INKSKBERNR,
  ID,
  StartDate
FROM [http://community.qlik.com/servlet/JiveServlet/download/593204-121240/Tmp.qvd.xml]
(qvd);

Left Join (tabTmp)
LOAD Distinct StartDate,
    ID, 
    If(ID=Previous(ID), If(StartDate=Previous(StartDate),Peek(EndDate),Date(Previous(StartDate)-1)), Date(Today())) as EndDate
Resident tabTmp
Order By ID, StartDate desc;

Due to duplicate rows your sample data is not unambiguously, so you might have some different solution in mind.

hope this helps

regards

Marco