Calculate future dates with different intervals

[#1]

Hi, maybe what i need its impossible...

I have a table with data like this:

AGENDA_ID	USER	PERIOD	UNITY	DAYS	NEXT_CALL
1	A	M	2		14/08/2012 10:12
2	B	A	1		23/10/2012 20:30
3	C	H	30		15/06/2012 07:05
4	D	M	1		14/07/2012 15:48
5	E	D	1	LXV	15/06/2012 12:00
6	F	D	1	J	14/06/2012 16:25

- PERIOD possible values = A (Yearly), M (Monthly), D (Daily), H (Hourly)

- UNITY = any integer value, but the more used values are 1, 2, 3, 4, 5, 6, 7, 14, 15, 30, 60

- DAYS only used when PERIOD = D and UNITY = 1 and it specify the week days to call (L = monday, M = tuesday, X = wednesday, J = thursday, V = friday, S = saturday, D = sunday)

Based in that values i need to calculate (autogenerate) the future dates from NEXT_CALL to nearly one year more (from actual date).

• So to User A, knowing it will be call every 2 months, i need to have a new table where appear that his next calls will be:

14/08/2012 10:12

14/10/2012 10:12

14/10/2012 10:12

(...)

14/06/2013 10:12

• The User B, will be called every one year, so only must appear:

23/10/2012 20:30

• The User C, will be called every 30 hours, so must appear:

15/06/2012 07:05

16/06/2012 13:05

17/06/2012 19:05

(...)

honestly, I don't know the end date

• The User E, will be called every L (monday), X (wednesday) and V (friday), resulting:

15/06/2012 12:00

18/06/2012 12:00

20/06/2012 12:00

(...)

12/06/2013 12:00

• The User F, will be called every J (thursday) so:
  

14/06/2012 16:25

21/06/2012 16:25

(...)

06/06/2013 16:25

Hopefully you understand what I need.

Obviously this is an example and combinations and users will be much more.

It's possible to do it with Qlikview?

Thanks by your attention

[#2]

WOOW!!! AMAAAAZING!!

I really will need a long time to study your script

Thank you very much!

CLAP, CLAP!!

[#3]

Hi Nirnaja, I need a little more of our help.

When i was trying to addapt your script the WEEKDAY and FUTUREDATE are nulls.

Attached my file.

Hope you can help me, thanks.

[#4]

hI,

PERIOD and UNIT filed are interchanged. for that reason future date is not caluculated.

i.e: PERIOD values should be A,D,M etc

And UNITvalues should be 1,2 3 etc.

cheers,

Niranjan M.

[#5]

One example i didn't explain was when PERIOD is D the UNITY could be 1, 2, 3,....,14,...

I changed your code to:

if
(Num(weekday(Timestamp(NEXT_CALL + (UNITY * (IterNo()-1)) ,'DD/MM/YYYY hh:mm'))) =  MODIFIED_DAYS,
weekday
(Timestamp(NEXT_CALL + (UNITY * (IterNo()-1)) ,'DD/MM/YYYY hh:mm'))) As WEEKDAY,
if
(Num(weekday(Timestamp(NEXT_CALL + (UNITY * (IterNo()-1)) ,'DD/MM/YYYY hh:mm'))) =  MODIFIED_DAYS,
Timestamp(NEXT_CALL + (UNITY * (IterNo()-1)) ,'DD/MM/YYYY hh:mm')) As FUTUREDATE  

But it doesn't calculate the future dates every X days of UNITY.

Attached the new file

Do you know how to get it?

Thanks for you help

[#6]

replace 😧 Balock with the following:

😧

Load
AGENDA_ID ,
if( UNITY = 1,
if( Num(weekday(Timestamp(NEXT_CALL + (UNITY * (IterNo()-1)) ,'DD/MM/YYYY hh:mm'))) =  MODIFIED_DAYS,
weekday(Timestamp(NEXT_CALL + (UNITY * (IterNo()-1)) ,'DD/MM/YYYY hh:mm'))),weekday(Timestamp(NEXT_CALL + (UNITY * (IterNo()-1)) ,'DD/MM/YYYY hh:mm'))) As WEEKDAY,
if(
UNITY = 1,
if( Num(weekday(Timestamp(NEXT_CALL + (UNITY * (IterNo()-1)) ,'DD/MM/YYYY hh:mm'))) =  MODIFIED_DAYS,Timestamp(NEXT_CALL + (UNITY * (IterNo()-1)) ,'DD/MM/YYYY hh:mm')),Timestamp(NEXT_CALL + (UNITY * (IterNo()-1)) ,'DD/MM/YYYY hh:mm')) As FUTUREDATE
Resident
Data
While (NEXT_CALL + (UNITY * (IterNo()-1))) < AddYears(NEXT_CALL,1) and PERIOD = 'D'

;

[#7]

Thanks, now is completely perfect!!

Sorry for late reply