topic Re: Time Compare Problem (SQL) in QlikView

Time Compare Problem (SQL)

Wed, 05 Jul 2017 07:43:06 GMT

Hi Everyone,

I'm getting crazy because - I guess - a small problem:

I import Data from a SQL-Database with OLE-DB - there is a data field (Date) with the format DD.MM.YYYY hh:mm.

My settings look like that:

SET TimeFormat='hh:mm';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm';

In another field a got some Entries per Month (SU_TIME) over 3 years.

Now I just want to show the average per month with AVG() and select one year as following:

AVG ({$<Date={2015}>}SU_TIME)

Dimenson is just Date.

The diagramm won't show any data to me.

Even simple if-statements do not work with Date ...

Hopefully you can help me!

Thanks a lot,

Jan

Re: Time Compare Problem (SQL)

henrikalmen — Wed, 05 Jul 2017 08:49:40 GMT

If you create a field named Year you could do like this:

AVG ({$<Year={2015}>}SU_TIME)

But if you don't want to create a year field, you need to do something like this:

AVG({$<Date={">=01.01.2015<=31.12.2015"}>}Date)

Re: Time Compare Problem (SQL)

Anonymous — Wed, 05 Jul 2017 08:52:05 GMT

a "dirty" solution could be perhaps:

AVG ({$<Date={"*2015*"}>}SU_TIME)

Re: Time Compare Problem (SQL)

Anil_Babu_Samineni — Wed, 05 Jul 2017 08:57:43 GMT

This expression should work

AVG ({$<Date={2015}>}SU_TIME)

But, I doubt whether Date has 2015 Date. May be change it to Year rather Date

Re: Time Compare Problem (SQL)

Wed, 05 Jul 2017 09:56:14 GMT

Hi Henrik,

I'll try tomorrow morning.

But why does it help to create another field? I already have mit "Date" Field with 2015, 2016, 2017 inside. You mean that Qlikview links them afterwards automatically?

Thank you very much!

Re: Time Compare Problem (SQL)

Wed, 05 Jul 2017 09:59:15 GMT

Ah - I see what you mean!

Like a string-search

But why cannot I compare it like I do?

Would be nice to habe something like If(Date>2015,..,...)

Thank you!!

Re: Time Compare Problem (SQL)

Wed, 05 Jul 2017 10:00:31 GMT

Yes - I guess it should but it doesn't..

Date has for example: 01.05.2015 08:00

Thanks!

Re: Time Compare Problem (SQL)

henrikalmen — Wed, 05 Jul 2017 10:01:25 GMT

Your date field contains day, month and year. If you have a field that contains only year, you can do as you tried. Otherwise you'll have to do a search in the field using set expression, in the way I suggested or with the actually simpler solution "*2015*" that Robin suggested.

Re: Time Compare Problem (SQL)

henrikalmen — Wed, 05 Jul 2017 10:02:48 GMT

You can do something like:

if(Year(Date)>2015, 'yes', 'no')

That would print "yes" if the year is larger than 2015, i.e. 2016 and onwards.

Re: Time Compare Problem (SQL)

Wed, 05 Jul 2017 10:27:26 GMT

Ahh - thank you very much!

I understood

Re: Time Compare Problem (SQL)

Wed, 05 Jul 2017 10:28:10 GMT

but if(Date>now(),..,..) should work - right?

Re: Time Compare Problem (SQL)

Anil_Babu_Samineni — Wed, 05 Jul 2017 10:34:10 GMT

No, It won't if Date has simple format and Now() returns Full timestamp. How come this works

You need to manipulate or play with Date format

Re: Time Compare Problem (SQL)

Anonymous — Wed, 05 Jul 2017 10:37:25 GMT

Using separate year fields is absolutely common practice.

Nothing is faster than selecting a field directly (without wildcards) via set analysis.

So this "waste of space" you're thinking about is negligible, because QlikView stores only distinct values with the help of bit stuffed pointers...

Re: Time Compare Problem (SQL)

Thu, 06 Jul 2017 00:38:05 GMT

All of your suggestions helped me a lot!

I firgured out as follows (in Script):

Year(Date) as Year,

Month(Date) as Month,