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: 
lfalmoguera
Creator
Creator

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

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:

customerdate
Luis01/01/2019
Carl01/01/2019
John05/01/2019
Luis03/01/2019
Carl08/01/2019
John10/01/2019
Luis15/01/2019
Carl16/01/2019
John20/01/2019

 

And I want to process it and convert it into:

customerdateStartdatEnd
Luis01/01/201902/01/2019
Carl01/01/201907/01/2019
John05/01/201909/01/2019
Luis03/01/201914/01/2019
Carl08/01/201915/01/2019
John10/01/201919/01/2019
Luis15/01/201931/12/2999
Carl16/01/201931/12/2999
John20/01/201931/12/2999

 

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

Kind regards

Labels (3)
10 Replies
johnca
Specialist
Specialist

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
Creator
Creator
Author

Hi John, 

sure! Sorry for not giving more info.

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

If you see for Carl

customerdate
Carl01/01/2019
Carl08/01/2019
Carl16/01/2019

 

Therefore  the result shall be:

 

customerdateStartdateEnd
Carl01/01/201907/01/2019
Carl08/01/201915/01/2019
Carl16/01/201931/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
Creator
Creator
Author

Anyone?
Thanks a lot in advance!
lfalmoguera
Creator
Creator
Author

Anyone with a little bit of help?

I am struggling 😞

jonathandienst
Partner - Champion III
Partner - Champion III

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;
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

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
Creator
Creator
Author

That works lovely!
So much appreciate it!
lfalmoguera
Creator
Creator
Author

Thanks a lot for the advice!
Will use it.