Add time to a timestamp

[#1]

Hi,

I'd like to add a variable period of time to a timestamp. Basically, I have a table with a flag indicating the period I want to add. The table should look like the one below.

Start_Time,           Period

01/01/2012 21:22,   M

02/03/2012 10:00    Y

03/09/2011 11:23     D

02/08/2012 01:11    H

M-Month

Y-Year

D-Day

H-Hour

Based on the above table I would like to compute the End_Time for each entry by adding the period to the Start_Time. Is there a way to convert this period into some sort of interval so that I can directly add them together using Timestamp(Start_Time+Period) to get the End_Time?

Regards,

Xue Bin

[#2]

Yes, that can be done.

See attached and/or see this load script.

variableTimeCalculation:
LOAD 
Timestamp#(Start_Time,'DD/MM/YYYY hh:mm') as initialTime,
//Time(Timestamp#(Start_Time,'DD/MM/YYYY hh:mm')) as timePart,
/*
below was for initial testing
AddMonths(Timestamp#(Start_Time,'DD/MM/YYYY hh:mm') ,1) as dateMonthAdded,
AddYears(Timestamp#(Start_Time,'DD/MM/YYYY hh:mm') ,1) as dateYearAdded,
date(Timestamp#(Start_Time,'DD/MM/YYYY hh:mm'))+1 as dateDayAdded,
date(Timestamp#(Start_Time,'DD/MM/YYYY hh:mm'))+(1/24) as dateHourAdded,
*/
if(Period='M',
//expression for add month
AddMonths(Timestamp#(Start_Time,'DD/MM/YYYY hh:mm') ,1)
,
                      if(Period='Y',
           //expression for add year
           AddYears(Timestamp#(Start_Time,'DD/MM/YYYY hh:mm') ,1) 
           ,
                     if(Period='D',
//                     expressio for add day
                     date(Timestamp#(Start_Time,'DD/MM/YYYY hh:mm'))+1
                     ,
                               if(Period='H',
                               //expression for add hour
                               date(Timestamp#(Start_Time,'DD/MM/YYYY hh:mm'))+(1/24)
                               )
                     )
                     )
           ) as myVariableTimeColumn
,
*
 INLINE [
    Start_Time, Period
    01/01/2012 21:22, M
    02/03/2012 10:00, Y
    03/09/2011 11:23, D
    02/08/2012 01:11, H
];

[#3]

Hi Robert,

Thank you for your prompt response.

I understand that the problem can be done this way.But what I need is to transform this period flag into a time interval so that I can use it for further processing. What I am doing next is to use a while loop to repeatedly generate timestamp in the next 2 years. Basically I am using expression

while Timestamp(Timestamp#(NEXT_CALL,'DD/MM/YYYY hh:mm')+PERIOD*IterNo(),'DD/MM/YYYY hh:mm')<END_TIME;

In this case, I really need a period to be interval, otherwise this while loop wouldn't work.

Regards,

Xue Bin 

[#4]

I think I don't understand your requirement... In my qvw the period between "initial time" and "myVariableTimecolumn" is a timeinterval. Apparently you mean something different?

Perhaps someone else can help 🙂

[#5]

Hi Robert,

Thanks for your help. But I still couldn't figure out one thing. When I use AddMonths() funtion, it returns me a date such as 01/01/2012. It doesn't actually give me a number such as 4940.8. Just wondering how did you manage to get it out as a number?

Regards,

Xue Bin

[#6]

Hi Jackyxuebin,

Actually when addMonths is applied, it may look like only a date, but under the hood, it is a datetime (change the number-format to datetime/timestamp and you'll see dateTime or something like 4940.8

One example, related to the QVW

- one datetime (column: myVariableTimeColumn) has the value 40790.474305556 and when the datetime format mask is applied, you'll see 4-09-2011 11:23:00

- So the part 40790 is for the date and the .474305556 is for the time

DateTimes in Qlikview are dual() so it can be seen as a number and as a datetime...

Does this answer it a bit?

jackyxuebin wrote:

doesn't actually give me a number such as 4940.8. Just wondering how did you manage to get it out as a number?

So to answer your quote above: I think it does give you a number (at least in my example it does...). Note that on the number-tab you have to set the format-mask correctly (to number with the right amount of decimals).

Perhaps then the interval you're looking for is just the one I'm going to post in a few minutes 🙂

[#7]

As announced in the previous post, I changed the qvw a bit so now there is an interval column.

Is that what you're looking for? (see attached)

[#8]

Hi Robert,

I understand that datetime is stored in dual(). I uncommented your initial testing code and found out that the dateMonthAdded and dateYearAdded column are displayed in 'DD/MM/YYYY' format whereas the variableTimeColumn is displayed in a number. Since the expression for dateMonthAdded and dateYearAdded is the same as that in variableTimeColumn for  'M' and 'Y', the result should be displayed in the same format right? I am just confused. Seems to be a simple problem, but I just couldn't figure it out.

In fact, what i was trying to do is to auto generate timestamp for the next year, according to the flag given. If the flag is 'M', I will add one month in each iteration. If the flag is 'H', I will add an hour in each iteration. Now I realize that the the interval for each month also varies. (It can be 28, 29, 30 or 31 days). Someone posted an answer in this thread using a diffrent approach. Thanks so much for your help:)

http://community.qlik.com/message/229788#229788

Regards,

Xue Bin