topic Re: Calculate future dates with different intervals in QlikView

Calculate future dates with different intervals

Wed, 13 Jun 2012 19:01:11 GMT

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/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

Re: Calculate future dates with different intervals

Mon, 18 Jun 2012 12:18:53 GMT

Can you help me?

Thanks

Re: Calculate future dates with different intervals

chematos — Mon, 18 Jun 2012 20:55:32 GMT

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

Re: Calculate future dates with different intervals

Tue, 19 Jun 2012 04:52:03 GMT

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

Thanks

Re: Calculate future dates with different intervals

Tue, 19 Jun 2012 08:04:58 GMT

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

Re: Calculate future dates with different intervals

Tue, 19 Jun 2012 08:22:30 GMT

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

Thanks,

Niranjan M.

Re: Calculate future dates with different intervals

Tue, 19 Jun 2012 08:25:05 GMT

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

Re: Calculate future dates with different intervals

Tue, 19 Jun 2012 08:26:36 GMT

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'

;

Re: Calculate future dates with different intervals

Tue, 19 Jun 2012 08:27:31 GMT

Hi,

the above addresses all your cases...

hope it will help you...

Niranjan M.

Re: Calculate future dates with different intervals

Tue, 19 Jun 2012 10:01:02 GMT

Hi Niranjan,

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

Regards,

Xue Bin

Re: Calculate future dates with different intervals

Tue, 19 Jun 2012 18:28:11 GMT

WOOW!!! AMAAAAZING!!

I really will need a long time to study your script

Thank you very much!

CLAP, CLAP!!

Re: Calculate future dates with different intervals

Wed, 20 Jun 2012 10:51:45 GMT

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.

Re: Calculate future dates with different intervals

Wed, 20 Jun 2012 11:00:05 GMT

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.

Re: Calculate future dates with different intervals

Wed, 20 Jun 2012 12:08:54 GMT

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

Re: Calculate future dates with different intervals

Wed, 20 Jun 2012 12:24:50 GMT

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'

;

Re: Calculate future dates with different intervals

Thu, 21 Jun 2012 06:30:29 GMT

Thanks, now is completely perfect!!

Sorry for late reply