I am trying to delve deeper into qlikview and stumbled upon a confusing issue.
I have a simple script pulling data from one table.
I created a table box object which shows me the three columns
"product_id", "created_date", "enabled_date"
I also created a listbox for filtering, where i can select the product_id.
When I now compare the contents of created_date and enabled_date with the original date in the database, the table object shows me wrong data for enabled_date.
To be specific, these are the different data from the two sources
Data Source
product_id
created_date
enabled_date
qlikview
216641
18.04.2012
08.10.2012
postgres database
216641
18.04.2012
0001-01-01
Seems to me like qlikview somehow has trouble handling the 0001-01-01 ?
If I change the select statement with
"CASE
WHEN enabled_date = '0001-01-01' THEN '2000-01-01'
ELSE enabled_date AS enabled_date"
END"
qlikview shows the correct date (01.01.2000) for this row.
Does anyone have more information about this issue?
If the date format used does not correspond to the one set in the operating system, QlikView will not be able to make a correct interpretation. In your case, 0001 will never be assumed as 2000. Unless the data source has the correct format, qlikview wont show the correct format how we want.
You can set it on your load statement itself using, Date and Time Function.
The date 0001-01-01 shouldn't be assumed as 2000-01-01 (the case when command was just a test to see if qlikview only has issues with the 0001-01-01), it is just a placeholder for an unknown date. Yet, it has the correct format and the column itself is of type date.
Thanks for the replies, but to give you more information: Every other date works perfectly fine, only 0001-01-01 somehow generates random data. So i don't need any casting. I can live with the Case When workaround but I just would like to know where qlikview generates the erroneous data from in order to be aware of other potential pitfalls.
