topic Data in Empty Field in QlikView

Data in Empty Field

agsearle — Fri, 13 Nov 2009 20:06:32 GMT

I'm importing a table where the data in one column is incomplete.

When the data is put into a Straight Table, there are lots of '-'s. I need these to show a blank date, '00/00/0000' for example.

Can this be done?

Data in Empty Field

prieper — Sat, 14 Nov 2009 01:13:41 GMT

Check Properties - Presentation - Display of Null-Values

HTH
Peter

Data in Empty Field

agsearle — Mon, 30 Nov 2009 16:51:10 GMT

Although this would then display the empty fields correctly, they wouldn't be searchable.

Is there a simple way to allow these empty fields to be searchable in a list box?

This is driving me mad, please help.

Data in Empty Field

prieper — Mon, 30 Nov 2009 19:04:07 GMT

If loading from ODBC you may use the NULLDISPLAY-function. Otherwise you may insert a formula in the script:

LOAD ...., IF(LEN(MyDate) = 0, '#N/A', MyDate) AS DateField ....

n.b.: do not think that is is wise to insert '00/00/0000' as date, as you may other problems lateron (building a calendar etc.)

HTH
Peter

Data in Empty Field

agsearle — Mon, 30 Nov 2009 19:15:57 GMT

Already tried that one, and it didn't work.

However, I've managed to get round the issue now. In this field I have various dates and blanks, and for the purposes of report needed to exclude any with the date '31/12/9999' (SAP system end date), but each time I used a list box I couldn't keep the blanks.

Therefore I used a calculated dimension in the straight table:
IF(Move_Out_Date<>'31/12/9999','No Date',Move_Out_Date)

I can now select 'No Date'. Can't think why I didn't try that before!