Generate Calendar (Start Date and Start End) for Effective Dates

[lfalmoguera]

Hi all, 

First of all thanks in advance for the future help.

I am struggling quite a littel bit with something that might be easy for top users.

I have this table:

customer	date
Luis	01/01/2019
Carl	01/01/2019
John	05/01/2019
Luis	03/01/2019
Carl	08/01/2019
John	10/01/2019
Luis	15/01/2019
Carl	16/01/2019
John	20/01/2019

And I want to process it and convert it into:

customer	dateStart	datEnd
Luis	01/01/2019	02/01/2019
Carl	01/01/2019	07/01/2019
John	05/01/2019	09/01/2019
Luis	03/01/2019	14/01/2019
Carl	08/01/2019	15/01/2019
John	10/01/2019	19/01/2019
Luis	15/01/2019	31/12/2999
Carl	16/01/2019	31/12/2999
John	20/01/2019	31/12/2999

Any idea? I guess it has to be easy but I cannot find the solution.

Kind regards

[johnca]

What are your criteria per customer? The dateEnd's are not related to the dateStart's by any obvious value. What determines dateEnd?

V/r,

John

[lfalmoguera]

Hi John, 

sure! Sorry for not giving more info.

dateEnd gets the value of the next dateStart -1 day.

If you see for Carl

customer	date
Carl	01/01/2019
Carl	08/01/2019
Carl	16/01/2019

Therefore  the result shall be:

customer	dateStart	dateEnd
Carl	01/01/2019	07/01/2019
Carl	08/01/2019	15/01/2019
Carl	16/01/2019	31/12/2999

the last value can be null() or be defined as a future defect value 

I am trying to do while and loop but there has to be an easier way.

Thanks in advance.

[lfalmoguera]

Anyone?
Thanks a lot in advance!

[lfalmoguera]

Anyone with a little bit of help?

I am struggling 😞

[jonathandienst]

Do something like this:

SET DateFormat = 'DD/MM/YYYY';
SET TimestampFormat = DateFormat & ' hh:mm:ss[.fff]';

Raw:
LOAD customer, 
     date
FROM [<a href="https://community.qlik.com/t5/New-to-QlikView/Generate-Calendar-Start-Date-and-Start-End-for-Effective-Dates/m-p/1586348#M375641" target="_blank">https://community.qlik.com/t5/New-to-QlikView/Generate-Calendar-Start-Date-and-Start-End-for-Effective-Dates/m-p/1586348#M375641</a>]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

Result:
LOAD customer, 
	date as dateStart,
	Date(If(customer  = Previous(customer), previous(date) - 1, MakeDate(2999, 12, 31))) as dateEnd
Resident Raw
Order By customer, date desc;

Drop Table Raw;

[MindaugasBacius]

The Master Calendar in your data model would look more reasonable if you use Today() instead of MakeDate(2999, 12, 31).
Else you will generate dates that are useless.

[avinashelite]

Check this document , this  show how to generate future dates with different intervals 

https://community.qlik.com/t5/QlikView-Documents/Generating-future-dates-based-on-different-interval...

[lfalmoguera]

That works lovely!
So much appreciate it!

[lfalmoguera]

Thanks a lot for the advice!
Will use it.