topic Re: Problem with $date tag in QlikView

Problem with $date tag

lawrenceiow — Fri, 25 Apr 2014 16:59:10 GMT

I have loaded a field which contains a date (from an MS Access database, Date/Time format) and have performed some calculations on it, in the script, to create other fields - I have used Month and Year functions, both worked as I would expect.

When I check the field by adding a List Box I can see that the dates appear as expected and are in date order not text order. When I change the Number setting in the properties to show the date as a "Number" with "0 precision" I get numbers in the 40,000 range, again this is what I would expect of a date field.

However, when I view the field in Document Properties (ctrl-alt-D) the field only has the $numeric and $integer tags - no $timestamp, $date tags. I have three other date fields from a different MS Access database that are also in the Date/Time format but this do have the $timestamp, $date tags.

So, my question is, do I need to be concerned that this particular field does not have the $date tag? If it is going to be a problem, how do I fix it?

Any help would be much appreciated.

Regards

Lawrence

Re: Problem with $date tag

Gysbert_Wassenaar — Fri, 25 Apr 2014 17:19:43 GMT

They're only tags, so you don't need to be concerned. If you really want those tags anyway then explicitly using a date function on those fields should work (I think).

Load ..some_fields..., date(MyMSAccessDate) as MyDate;

SQL Select * from MyMSAccessTable;

Re: Re: Problem with $date tag

lawrenceiow — Fri, 25 Apr 2014 17:36:47 GMT

Thanks for your reply Gysbert Wassenaar. If it's not going to matter as it's just a tag then that's great. However, I was concerned that if QlikView hadn't recognised it as a date properly and therefore hadn't added a $date tag there might be a problem somewhere along the line.

I tried the date function as you suggested but this did not work, I also tried date# but this did not work either - still just $numeric and $integer.

Re: Problem with $date tag

Gysbert_Wassenaar — Fri, 25 Apr 2014 17:52:25 GMT

Well, that's disappointing. But you won't have to worry about Qlikview not recognizing the field as a date. Qlikview knows only about text and numbers. Dates are simply numbers, usually with a textual display format too, but still numbers in the background.

Re: Problem with $date tag

lawrenceiow — Mon, 28 Apr 2014 07:48:19 GMT

Thanks again for your advice on this issue, Gysbert. As it seems to be displaying the numeric and textual values correctly I'll not worry about the date$ bit. I did wonder if this was something that might be resolved if I update my QlikView version as I'm on 11.0 SR1.

Re: Problem with $date tag

Fri, 03 Jul 2015 12:20:46 GMT

Hallo,

I have exactly the same problem with DATE fields coming out of an oracle database. Both fields are DATE NULLABLE in the db but one is tagged with $date, the other not.

I do not agree with you in your opinion that it doesn't matter. The $date tag is one of the metadata QV creates on reload. If I want to trust the reload and want to use tags, I have to rely on the product that's loading and tagging the data. So I think it's a bug or a secret reason why QV decides not to tag this field.

For me it has got to to with quality check. I understand why Lawrence is worried that there might be something wrong in the data coming out the db.

So I think, QV should clarify this. If it's a bug->fix it, if it's because of a secret reason->explain it in the product documentation.

Re: Problem with $date tag

Gysbert_Wassenaar — Fri, 03 Jul 2015 12:33:25 GMT

If I want to trust the reload and want to use tags, I have to rely on the product that's loading and tagging the data

If you want to trust tags then do the tagging yourself. That's the only way to make sure. Qlik cannot be responsible for whatever metadata information (if any) a third party ODBC or OLEDB driver returns about fields.

If it's a bug

So file a bug and find out it you're right and it is a bug.