topic AW:Re. :Calendar Date - internal link QV10 in QlikView

Calendar Date - internal link QV10

Wed, 20 Oct 2010 13:22:18 GMT

Hallo,

I have a big problem with QV10. I just set up a testserver and just updated my QV9.5-Applikationions. Everything was just fine and ultra fast. Good job a this point by the way. But then I stuck with a big problem...

Calendar-Date-Fields did not link correctly:

Calendar: Fact-Table:
Date Year Date Price
01.01.2010 2010 01.01.2010 $10

Should be:
Date Year Price
01.01.2010 2010 $10

But got to:
Date Year Price
01.01.2010 2010 -
01.01.2010 - $10

In QV9.5 I didn't need to format my date from fact-table. After port to QV10 and notice of this problem I did so and formated both "date"-fields to date() but that did not the trick. So I tried text(date()) and that did it.

But now I have a new problem. I use Intervallmatch on some of my apps. So now I have to use Intervallmatch on text()-formated field, what is impossible. So I could think of use num() but this one is to "hot" for me. "num(01.01.2010)"

Someone has same problem or a solution for me?

Greetings,
Jonas

AW:Calendar Date - internal link QV10

Wed, 20 Oct 2010 15:27:39 GMT

Hallo,

I tried some more things like hash128() and others. But I am not able to get intervallmatch to work. I have 3 datefields here. Date in calendar and 1 start-date and 1 end-date so intervallmatch have to compare all these 3 fields. It just doesn't work. QV10 is not able to "see" that date(01.01.2010) is the same as date(01.01.2010).

I just don't get it :-(.

Greetings,
Jonas

AW:Calendar Date - internal link QV10

Fri, 22 Oct 2010 09:41:10 GMT

I have made an example, so you can see what I mean. QVW is linked...

ODBC 32 and 64 tested. QV9.5 and QV10 Document tested. And others.

To always format my date-keys as text() is not that good option for me 😞 . Suprisingly I got my Intervall match to work with all fields formated date(). Just still didn't get that bug.

By the way... we use AS400 DB2 IBM iSeries ODBC.

Greetings,
Jonas

AW:Calendar Date - internal link QV10

hic — Fri, 22 Oct 2010 14:22:46 GMT

I looked at your application and it seems as if the dates really are timestamps - same date but different minutes and seconds. This is why the values do not link. I have the following values:

02.10.2010 00:00:00
02.10.2010 00:01:23

Try Date(Floor(DATUM)) and it will probably work.

Cheers

Henric

AW:Re: AW:Calendar Date - internal link QV10

Tue, 26 Oct 2010 08:17:59 GMT

I'll try your solution and I can imagine that it should work that way. But the strange thing is... my dates from database ARE dates and not timestamps. Till QV9.5 SR6 I never had problems on this one. Even not formating my dates from source did it. In past I had to do nothing and it worked like it should be.

Now the new scriptenvironment seperated from client itself leads to that problem. Could be worse I think but I have to reengeneer all links in all my apps for internal date to calendar. 😞 .

Re. :Calendar Date - internal link QV10

Thu, 18 Nov 2010 00:31:01 GMT

Hello

Excatly the same problem here. My data come from and iSerie.

I migrate from QV9 to QV10 and i have the problem with all my calendar scripts.

My date key don't match any more. as you can see it seem that there is 00:01:23 in my date field !!!

Is the floor fonction resolve the problem with QV10 ?

Thanks

AW:Re. :Calendar Date - internal link QV10

Thu, 18 Nov 2010 08:23:51 GMT

Hey Marlot,

yes, floor did the job. I had to rewrite alle my stuff. Was really disapointing to see QlikView getting another Problem with iSeries. Now not just OleDB-Driver doesn't work at all, but even ODBC has it's problems :-(.

IBM OleDB doesn't work at all

IBM ODBC has a date-format-problem (floor can fix it)

If you use an intervalmatch on datefilds you have the biggest problems. Everytime I use if or SET on an date-field in QV10 I am scrupling.

Because I had other problems with a firebird x64 ODBC we downgraded on QV9 again. It seems there are more then one Problem with the new scriptengine. 😞 Firebird driver even totaly kills QV10 if you start dataimport.

Greetings,
Jonas

Re. :AW:Re. :Calendar Date - internal link QV10

Thu, 18 Nov 2010 09:37:41 GMT

Thanks for your help. Do you call the support for this issue ?

I downgrade too in QV9 for loading the data !!!

Marlot

AW:Re. :AW:Re. :Calendar Date - internal link QV10

Thu, 18 Nov 2010 11:50:27 GMT

Hallo,

Henric Cronström (hic) just gave me this advice. He's from QlikTech I think. So I hope they look at this problem.

But it would not be the worst idea to tell support about this matter, so they have a closer look. I have not planned so. Perhaps you can make this call.

Greetings,
Jonas

PS. our dates are NO timestamps. QlikView make this mistake. We load DATE-Field and get a Timestamp-Field in QV10. It is always 00:01:23 ... for all Dates.

AW:Re. :AW:Re. :Calendar Date - internal link QV10

Thu, 18 Nov 2010 12:16:03 GMT

Have you tried to modify the TimeStampFormat not to include seconds ? Maybe it is less work than adding floor() functions everywhere.

-Alex

Re. :AW:Re. :AW:Re. :Calendar Date - internal link QV10

Thu, 18 Nov 2010 12:21:41 GMT

I call the support

Regards

Marlot

Re. :AW:Re. :AW:Re. :Calendar Date - internal link QV10

hic — Thu, 18 Nov 2010 12:28:16 GMT

I would appreciate if you called the support. Then we will get the case into our system in a proper way.

If you could make an extract to QlikView with a simple SELECT, and then use an identical SELECT importing to e.g. Excel and send us both files, then we can easier see where something goes wrong.

Changing the TimeStampFormat will not change anything. The format is just a textual representation of the number and it is the number that is used as key.

Thanks in advance

HIC from R&D

Re. :Re: Re. :AW:Re. :AW:Re. :Calendar Date - internal link QV10

Mon, 22 Nov 2010 13:55:27 GMT

Ok send. 22/11/2010 13:45

AW:Calendar Date - internal link QV10

Thu, 06 Jan 2011 11:23:49 GMT

Could you tell me how to implement the 'Floor' function into the below?. I am getting the same problem with the hh:mm:ss not matching up and would like them zeroing out.

can you tell me how to re-write this line adding the floor function in?

to_char(work_day,'yyyy-mm-dd hh:mm:ss') WORK_DAY,

thanks

Lee

AW:Re: AW:Calendar Date - internal link QV10

Thu, 06 Jan 2011 15:08:14 GMT

QV10 SR1 is out and shoud fix this bug. RLN say so... I'll start testing tomorrow.

AW:Re: AW:Calendar Date - internal link QV10

Thu, 06 Jan 2011 15:14:04 GMT

I know sr1 fixes this issue, just wanted to know how to fix it currently in 9

can someone please correct my statement to add the 'floor' function in for me so I will be able to compare across dates without the hh:mm:ss issue

thanks

Lee

AW:Re: AW:Calendar Date - internal link QV10

Thu, 06 Jan 2011 15:33:03 GMT

In QV 9 there was no such problem I described.

So I do not really know, what you want to get out of your Statement. Bur you can try something like date(date#(string,)

date(date#(work_day,'yyyy-mm-dd') or timestamp(timestamp#(work_day,'yyyy-mm-dd hh:mm:ss'))

Try this for both of your "should-match-filds".

AW:Re: AW:Calendar Date - internal link QV10

Thu, 06 Jan 2011 15:47:38 GMT

sorry, I should have been clearer

in Qlikview 9 there was no problem, I would create a qvd file with the below select statment

select

work_day

from wherever

the work_day field once reloaded would be in the format of 2007-12-01 00:00:00 which is the format which works and we calculate on in all our graphs.

However, in qlikview 10 (not sr1) when I run the same statement qlikview returns the date as this

39437.000000

I got around this a little bit by changing my select statement to:

select

to_char(work_day,'yyyy-mm-dd hh:mm:ss') Work_Day,

from wherever

but now the work_day field returns the date in the following format:

2007-01-26 12.01.00

2007-01-27 12:02.00 etc etc

I dont know why or where qlikview 10 is getting this new hour and minute info from but I dont want it, I just want that to be returned as 00:00:00 as in qlikview 9.

the floor function was mentioned earlier in this post and it sounded like it would fix my particular issue, I just wanted to know how to add it into my select statement to get it to work

thanks in advance

Lee

AW:Re: AW:Calendar Date - internal link QV10

Tue, 11 Jan 2011 09:45:55 GMT

Can anyone help with the below still?

The floor function was mentioned earlier on in this post and i want to implement it into my qliview app to solve my date format issue

the work_day field once reloaded would be in the format of 2007-12-01 00:00:00 which is the format which works and we calculate on in all our graphs.

However, in qlikview 10 when I run the same statement qlikview returns the date as this

39437.000000

I got around this a little bit by changing my select statement to:

select

to_char(work_day,'yyyy-mm-dd hh:mm:ss') Work_Day,

from wherever

but now the work_day field returns the date in the following format:

2007-01-26 12.01.00

2007-01-27 12:02.00 etc etc

AW:Re: AW:Re: AW:Calendar Date - internal link QV10

Tue, 11 Jan 2011 09:57:01 GMT

Date(Floor(work_day)) did it for me.

Internal dates can stay just date(internal_date) and dates coming from ODBC have to be changed to Date(Floor(DATUM))

If you want your timestamp you can try

timestamp(floor(work_day))

Since last post I tests SR1 and it solves this problem. you should think of updateing you system. Sould be worth it. I'll do so.