Tuesday, August 21, 2012

QlikView Date Headaches

Short Description:  In QlikView, some dates formatted with the date or date# functions don't load as prescribed.

I often run into date display issues with QlikView and wonder how it handles dates internally.  I wonder this because I relatively frequently run into the situation where I run a load script and date fields default to date format in the primary file, but on saving the product of a load script to a qvd file dates go wonky and start displaying as integers, or datetimes display as dates and/or vice versa.  I have some trouble understanding how data types "degrade" between the load and the save when being consumed by the *same application*, but explicitly forcing them usually works.

Queue today's problem:  In loading a relatively simple script with a few joins, all the date fields imported differently (even 'mm/dd/yyyy' in the same column as an int date, so...fun).  No problem; run them through date(field, 'MM/DD/YYYY') in the last operation.  Not so much, but surely date#(field, format) will work?  That was a no.  After much frustration, the culprit appears to have been a datetime field loaded from a query that used the tsql MAX operator.  Forcing it to datetime immediately after loading from the tsql was the only way I found to get around it.  Forcing it later in the script/in a different operation/right before saving to the qvd did no good.  

Your mileage may vary: but in the case of date fields (in QlikView) that just won't behave even when explicitly forced, it may make sense to quickly check if the initial load from the source system contains any aggregation operators.

No comments:

Post a Comment