topic Re: difference between date type values in QlikView

difference between date type values

Tue, 25 Sep 2012 11:55:19 GMT

hello,

i have date-time data coming in from sql server in the form of integar

ex: one column 'abc' contains values such as : 1002171551 (date value in: yymmddhhmm)

2nd column 'def' contains values such as : 100217 or 1002170000 (date value in: yymmdd)

now i need to calculate difference between these two columns and i want sql server to treat these as date columns and not integar ones.

right now when i do the difference i get result purely as in subtraction of 2 integar values. but i want the result to be calculated as a difference between dates.s

any suggestions?

Re: difference between date type values

jagan — Tue, 25 Sep 2012 12:17:36 GMT

Hi Tushar,

First in QV scripting convert the date values in the form of numbers to date by using Date# function, so now the values are in date format now you can calculate the difference using Interval() easily.

Example:

Date#( DateField, 'YYMMDD' )

Hope this helps you.

Regards,

Jagan.

Re: difference between date type values

Tue, 25 Sep 2012 12:20:57 GMT

hi jagan, thanks for the answer.

but the problem is that these number values are actually dates (1002171551 - '10' as year, '02' as month, '17' as date, '15' as hour, '51' as minutes) in sql. so if i use date# on these, result will be wrong data. hope it makes sense.

-tushar

Re: difference between date type values

jagan — Tue, 25 Sep 2012 12:24:32 GMT

Hi,

Date#() should not be used in SQL query, it should be used in Qlikview Load statement.

Regards,

Jagan.

Re: difference between date type values

Tue, 25 Sep 2012 12:32:34 GMT

hi,

i have used it in QV scripts and like i said before, it does not treat it differently.

here's an example: date#('1002171551', 'YYMMDD') gives output as 1002171551.

and if i calculate difference between two columns containing this type of data, i dont get the output at all

Re: difference between date type values

jagan — Tue, 25 Sep 2012 12:44:30 GMT

Hi,

Try this

=date(date#('1002171051', 'YYMMDDhhmm'))

Hope this helps you.

Regards,

Jagan.

Re: difference between date type values

Tue, 25 Sep 2012 12:56:04 GMT

this partially helps. but what i need to find is the difference between two columns that contain data like this.

say col abc = 1002171551

col def = 1002171051

now 'abc' - 'def' should be calculated as difference between calendar dates and the result should be displayed in calendar date format.

any ideas?

Re: difference between date type values

Tue, 25 Sep 2012 13:04:43 GMT

hi ,

thanks for your suggestion, and i mixed interval() with timestamp#() to get the desired result, just not in the desired format. still working on it.. thanks Jagan

Re: difference between date type values

jagan — Tue, 25 Sep 2012 13:13:11 GMT

Hi,

You will get difference between two dates in terms of days/months/years, I cannot understand what you are asking "difference between calendar dates and the result should be displayed in calendar date format.". Please clarify.

Regards,

Jagan.

Re: difference between date type values

whiteline — Thu, 27 Sep 2012 07:09:51 GMT

Hi.

date#('1002171551', 'YYMMDD') gives output as 1002171551.
and if i calculate difference between two columns containing this type of data

To use date#() here you should truncate your string (it's a known issue):

date#(left('1002171551', 6), 'YYMMDD')

To check that your string dates was converted into numerical dates (so that you can add and subtract them), you can just try to change its text format. For example, using num() functiont that return numerical representation.

You can easily try it in a text box:

=num(date#(left('1002171551', 6), 'YYMMDD')) << this returns 40226

=num(date#('1002171551', 'YYMMDD')) << this doesn't work and returns null.

Re: difference between date type values

Thu, 27 Sep 2012 08:34:56 GMT

Hi,

Based on your example, I loaded it inline, used the makedate function to get the proper date format. Now you can compute for the date difference. See the attached file for your reference.

Regards,

Janzen