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]

Can you help me?

Thanks

[chematos]

try this:

Add a colum with the last day call.

i.e: Today(), and we call it Last Call

To calculate next call, try this expression:

If(PERIOD='M',Addmonths([Last Call], UNITY), If(PERIOD='A',AddYears([Last Call], UNITY),

If(PERIOD='D',AddDays([Last Call], UNITY),If(PERIOD='H',[Last Call]+UNITY)

You will need some code to change the column Last Call when the next call was done.

Is not the solution but may be helps you, I hope

[#4]

Hi, the problem is i need to calculate (predict) the calls during a year and not only the next call.

Thanks

[#5]

Hi,

The below script doesn't address all the problem. It generates all the call time within one year repeatedly for all the customers. I'm not sure how to generate call time on specific weekday thougth. And If anyone can help me on generating appropriate interval for month and year. Here I assumed a month is 30 days and a year is 365 days. Hope it will help in some way.

tempTable:
LOAD * INLINE [
    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
];

tempTable1:
LOAD *,
IF(PERIOD='A',365,IF(PERIOD='M',30,IF(PERIOD='D',1,IF(PERIOD='H',1/24,'NULL')))) AS PERIOD_NUM,
Timestamp(Timestamp#(NEXT_CALL,'DD/MM/YYYY hh:mm')+365,'DD/MM/YYYY hh:mm') AS END_TIME
Resident tempTable;

drop table tempTable;

table:
LOAD
AGENDA_ID,
USER,
Timestamp(Timestamp#(NEXT_CALL,'DD/MM/YYYY hh:mm')+PERIOD_NUM*IterNo()*UNITY,'DD/MM/YYYY hh:mm') AS NEXT_DATE
RESIDENT tempTable1
while Timestamp(Timestamp#(NEXT_CALL,'DD/MM/YYYY hh:mm')+PERIOD_NUM*IterNo()*UNITY,'DD/MM/YYYY hh:mm')<END_TIME;

Drop table tempTable1;

Regards,

Xue Bin

[#6 · Accepted solution]

I have made as you required, please find the qvw file.

Thanks,

Niranjan M.

[#7]

Hi Niranjan,

I don't have a license and couldn't open your qvw. Could you share your code here? I also would like to learn from it:)

Regards,

Xue Bin

[#8]

Days:

Mapping

LOAD * INLINE [

    Days, m_days

    L, 0

    M, 1

    X, 2

    J, 3

    V, 4

    S, 5

    D, 6

];





Data:

Load

  AGENDA_ID,

  USER,

  PERIOD,

  UNITY,

  DAYS,

  Timestamp#(NEXT_CALL,'DD/MM/YYYY hh:mm') As NEXT_CALL

;

LOAD * INLINE [

    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

];



left Join(Data)

LOAD

AGENDA_ID,

ApplyMap('Days',Mid(DAYS,IterNo() -1,1)) As MODIFIED_DAYS

Resident

Data

While IterNo() - 1 <= Len(DAYS)

;



M:

Load

AGENDA_ID,

weekday(Timestamp(AddMonths(NEXT_CALL,UNITY * (IterNo()-1)),'DD/MM/YYYY hh:mm')) As WEEKDAY,

Timestamp(AddMonths(NEXT_CALL,UNITY * (IterNo()-1)),'DD/MM/YYYY hh:mm') As FUTUREDATE

Resident

Data

While AddMonths(NEXT_CALL,UNITY * (IterNo()-1)) < AddYears(NEXT_CALL,1) and PERIOD = 'M'

;



A:

Load

AGENDA_ID,

weekday(Timestamp(AddYears(NEXT_CALL,UNITY * (IterNo()-1)),'DD/MM/YYYY hh:mm')) As WEEKDAY,

Timestamp(AddYears(NEXT_CALL,UNITY * (IterNo()-1)),'DD/MM/YYYY hh:mm') As FUTUREDATE

Resident

Data

While AddYears(NEXT_CALL,UNITY * (IterNo()-1)) < AddYears(NEXT_CALL,1) and PERIOD = 'A'

;

😧

Load

AGENDA_ID ,

if(

UNITY = 1 and 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(

UNITY = 1 and 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

Resident

Data

While (NEXT_CALL + (UNITY * (IterNo()-1))) < AddYears(NEXT_CALL,1) and PERIOD = 'D'

;



H:

Load

AGENDA_ID ,

weekday(Timestamp(NEXT_CALL + (1/24 * UNITY * (IterNo()-1)) ,'DD/MM/YYYY hh:mm')) As WEEKDAY,

Timestamp(NEXT_CALL + (1/24 *UNITY * (IterNo()-1)) ,'DD/MM/YYYY hh:mm') As FUTUREDATE

Resident

Data

While (NEXT_CALL + (1/24 * UNITY * (IterNo()-1))) < AddYears(NEXT_CALL,1) and PERIOD = 'H'

;

[#9]

Hi,

the above addresses all your cases...

hope it will help you...

Niranjan M.

[#10]

Hi Niranjan,

Didn't realize I should do them seperately. Nice approach:)

Regards,

Xue Bin