topic Re: How to substract a second (or a minute) from a date field?!? in QlikView

How to substract a second (or a minute) from a date field?!?

hansdevr — Mon, 07 Sep 2015 06:35:18 GMT

I need to fill a new field in a table with the date from a field in a previous record.

The latter contains a date in the following format: yyyy-mm-dd hh:mm:ss:xxxxxxx (not sure what those xx's are). Example:

2014-11-14 02:00:50:3930000

I need to use that value in a (new) field in the current record, but I need to substract a second or a minute from that value.

I have been searching, but couldn't find an answer...

Re: How to substract a second (or a minute) from a date field?!?

Mon, 07 Sep 2015 06:41:24 GMT

Hi,

How to calculate the time difference between two different date?

Use the above link, it might help u.

Re: How to substract a second (or a minute) from a date field?!?

sunny_talwar — Mon, 07 Sep 2015 06:44:21 GMT

Not sure what you mean when you say you have to subtract a second or a minute? In your above example: 2014-11-14 02:00:50:3930000 what would be the output you would be expecting?

Re: How to substract a second (or a minute) from a date field?!?

Mon, 07 Sep 2015 06:44:27 GMT

Hi,

it might help you.

Subtract two timestamps to output difference in minutes

Re: How to substract a second (or a minute) from a date field?!?

hansdevr — Mon, 07 Sep 2015 06:45:43 GMT

Hi Harshita and thank you for your swift reply.

I have looked at that link, and unfortunately, it gives an example on how to calculate a difference, not how to substract a value (of a second or a minute).

Thanks anyway!

Re: How to substract a second (or a minute) from a date field?!?

hansdevr — Mon, 07 Sep 2015 06:49:01 GMT

If the first value is 2014-11-14 02:22:50:3930000 then the second value should be 2014-11-14 02:22:49:3930000

(which subtracts one second) OR the outcome should be 2014-11-14 02:21:50 3930000, which is the subtraction of a minute.

Re: How to substract a second (or a minute) from a date field?!?

sunny_talwar — Mon, 07 Sep 2015 06:52:23 GMT

I think you need to create a master Time Table: The Master Time Table

This works as a master calendar, where you create missing time values. You can decide your smallest denomination to be Seconds or Minute.

HTH

Best,

Sunny

Re: How to substract a second (or a minute) from a date field?!?

hansdevr — Mon, 07 Sep 2015 06:55:47 GMT

Actually, I want to do the subtraction using a variable.. It's not a matter of a 'missing' time value - I just want to do a calculation on the value of a field within a record..

Re: How to substract a second (or a minute) from a date field?!?

Mon, 07 Sep 2015 06:59:37 GMT

First set up format like

for Minutes Calculation

vNowTime= num(timestamp#('2015/09/07 11:55:25 PM','YYYY/MM/DD hh:mm:ss TT'));
vPrevTime = num(timestamp#('2015/09/07 11:55:25 PM','YYYY/MM/DD hh:mm:ss TT'));

sum(vNowTime - vPrevTime )

vNowTime= num(timestamp#(INVTIME,'YYYY/MM/DD hh:mm:ss TT'));

vPrevTime = num(timestamp#(INVTIME,'YYYY/MM/DD hh:mm:ss TT'));

for Days Calculation

sum(Date(Today())- date(date#(INVDATE,'YYYYMMDD'),'DD/MM/YYYY'))

hope it works

Re: How to substract a second (or a minute) from a date field?!?

hansdevr — Mon, 07 Sep 2015 06:59:47 GMT

A bit like AddMonths(EndDate,-1), but then for a minute or a second, if you catch my drift..

Re: How to substract a second (or a minute) from a date field?!?

sunny_talwar — Mon, 07 Sep 2015 07:02:59 GMT

Try this to subtract a second:

=Timestamp(Timestamp#('2014-11-14 02:22:50:3930000', 'YYYY-MM-DD hh:mm:ss:fff') - Interval#('1', 'ss'), 'YYYY-MM-DD hh:mm:ss:fffffff')

To subtract a minute, try this:

=Timestamp(Timestamp#('2014-11-14 02:22:50:3930000', 'YYYY-MM-DD hh:mm:ss:fff') - Interval#('1', 'mm'), 'YYYY-MM-DD hh:mm:ss:fffffff')

Re: How to substract a second (or a minute) from a date field?!?

hansdevr — Mon, 07 Sep 2015 07:34:20 GMT

For a subtraction, your solution worked best, Sunny!

Now trying to implement this in a LOAD script....

Re: How to substract a second (or a minute) from a date field?!?

sunny_talwar — Mon, 07 Sep 2015 07:37:41 GMT

Try this in the script:

vTimeStamp = Timestamp#('2014-11-14 02:22:50:3930000', 'YYYY-MM-DD hh:mm:ss:fff')

vSubSeconds = Interval#('1', 'ss')

vSubMinutes = Interval#('1', 'mm')

vOutput1 = TimeStamp($(vTimeStamp) - $(vSubSeconds), 'YYYY-MM-DD hh:mm:ss:fffffff')

vOutput2 = TimeStamp($(vTimeStamp) - $(vSubMinutes), 'YYYY-MM-DD hh:mm:ss:fffffff')

LET vTimeStamp = Timestamp#('2014-11-14 02:22:50:3930000', 'YYYY-MM-DD hh:mm:ss:fff');

LET vSubSeconds = Interval#('1', 'ss');

LET vSubMinutes = Interval#('1', 'mm');

LET vOutput1 = TimeStamp($(vTimeStamp) - $(vSubSeconds), 'YYYY-MM-DD hh:mm:ss:fffffff');

LET vOutput2 = TimeStamp($(vTimeStamp) - $(vSubMinutes), 'YYYY-MM-DD hh:mm:ss:fffffff');

Re: How to substract a second (or a minute) from a date field?!?

hansdevr — Mon, 07 Sep 2015 07:39:15 GMT

Thank you very much!! I am impressed with the speed this community responds to questions!!

Re: How to substract a second (or a minute) from a date field?!?

sunny_talwar — Mon, 07 Sep 2015 07:43:09 GMT

Actually this will work:

LET vTimeStamp = Num(Timestamp#('2014-11-14 02:22:50:3930000', 'YYYY-MM-DD hh:mm:ss:fff'));

LET vSubSeconds = Num(Interval#('1', 'ss'));

LET vSubMinutes = Num(Interval#('1', 'mm'));

LET vOutput1 = TimeStamp($(vTimeStamp) - $(vSubSeconds), 'YYYY-MM-DD hh:mm:ss:fffffff');

LET vOutput2 = TimeStamp($(vTimeStamp) - $(vSubMinutes), 'YYYY-MM-DD hh:mm:ss:fffffff');

Re: How to substract a second (or a minute) from a date field?!?

hansdevr — Mon, 07 Sep 2015 08:21:24 GMT

Your solution works, but not during a load script, in which I can't use your variable vTimeStamp..

Let's suppose I have this VERY simplified script:

LOAD
"creation_date";
SQL SELECT *
FROM "blahblahblah";

And I would like to have it look something like this:

LOAD
"creation_date",
(subtract one minute from creation_date) as "end_date_min",
(subtract one second from creation_date) as "end_date_sec";

SQL SELECT *
FROM "blahblahblah";

(which is of course total nonsense script), how would I go about it?

Re: How to substract a second (or a minute) from a date field?!?

sunny_talwar — Mon, 07 Sep 2015 08:40:39 GMT

Try this may be:

LOAD
"creation_date";
SQL SELECT *
FROM "blahblahblah";

And I would like to have it look something like this:

LOAD
"creation_date",
Timestamp(Timestamp#(creation_date, 'YYYY-MM-DD hh:mm:ss:fff') - Interval#('1', 'ss'), 'YYYY-MM-DD hh:mm:ss:fffffff') as "end_date_sec",
Timestamp(Timestamp#(creation_date, 'YYYY-MM-DD hh:mm:ss:fff') - Interval#('1', 'mm'), 'YYYY-MM-DD hh:mm:ss:fffffff') as "end_date_min";

SQL SELECT *
FROM "blahblahblah";

Important thing to note here is that we are creating two new columns which are one second (end_date_sec) and one minute (end_date_min) behind creation_date for each row. Is this what you are looking to do?

Re: How to substract a second (or a minute) from a date field?!?

hansdevr — Mon, 07 Sep 2015 08:47:45 GMT

Yes, that is EXACTLY what I'm looking for!

But alas, I got this when I looked at table example:

Re: How to substract a second (or a minute) from a date field?!?

sunny_talwar — Mon, 07 Sep 2015 08:52:23 GMT

Your TimeStamp field is this format: hh:mm:ss.fffffff?? no date attached to it? If this is how it is, then try this:

LOAD
"creation_date";
SQL SELECT *
FROM "blahblahblah";

And I would like to have it look something like this:

LOAD
"creation_date",
Time(Time#(creation_date, 'hh:mm:ss:fff') - Interval#('1', 'ss'), 'hh:mm:ss:fffffff') as "end_date_sec",
Time(Time#(creation_date, 'hh:mm:ss:fff') - Interval#('1', 'mm'), 'hh:mm:ss:fffffff') as "end_date_min";

SQL SELECT *
FROM "blahblahblah";

Re: How to substract a second (or a minute) from a date field?!?

hansdevr — Mon, 07 Sep 2015 08:59:45 GMT

Still the same result...