topic Re: Empty Date Field Problem in QlikView

Empty Date Field Problem

Mon, 14 Apr 2014 16:55:12 GMT

We had a problem with having an empty field for a date (QV 11 SR5).

When reading the Excel spreadsheet, we can directly use MyDate>0 to see if there is an actual date in the field. This calculation works in both straight table chart and graphs.

When we stored the Excel spreadsheet data (Load as * and Store as QVD) and then read the .qvd file, this calculation only works in straight table charts. It will not work with any graph or gauge.

The Excel date is formatted 1/1/2000 but the .qvd changed the format to 1-Jan-2000 (no read or write format was specified).

We loading the .qvd we can use an IF(MyDate>0,1,0) as MyDate_cnt to flag if the field is empty, but cannot do anything to see if the date is empty in a graph after loading. Is this a QV error? No problems with the Excel data, but errors with the .qvd data.

Thanks,

Raymond

I have added the sample project. The QVD gauge should equal the Final Equation in the table (Just like the Excel). Message was edited by: Raymond Wright

Re: Empty Date Field Problem

whiteline — Mon, 14 Apr 2014 17:07:09 GMT

Hi.

Could you upload the sample where this expression is not working with a graph ?

Re: Empty Date Field Problem

Mon, 14 Apr 2014 17:46:14 GMT

I have attached the sample file

Re: Empty Date Field Problem

whiteline — Mon, 14 Apr 2014 18:19:06 GMT

Hi.

The first thing is that your DateEGM and DateMGR fields are stored as TEXT instead of date format (dual text+numeric).

So there is no way to make a comparison of that fields with 0.

The easiest way to notice it is to use different alignment on presentation tab, text to the left and number to the right.

Or go to the document properties tables tab and see what tags are assigned to the fields.

You have to use date#(DateEGM, 'DD-MMM-YY') function to convert the string to date (either is script or in expression, but the best is to store the qvd properly).

Re: Empty Date Field Problem

Mon, 14 Apr 2014 18:32:27 GMT

Thank you for the help. This is a nightly load and was hoping to use the Load * to keep me out of dealing with the changing source file (not under my control) and then store directly to .qvd common file. But I would probably spend more time helping the end users debug the problems when the source file changes anyway.

Thanks again.

Re: Empty Date Field Problem

whiteline — Mon, 14 Apr 2014 18:39:14 GMT

The second was if the comparison logic is not subject to change move the calculation to script.

It allows you to simplify all your expressions with just a sum(DateFlag).