Data in Empty Field

[agsearle]

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?

[prieper]

Check Properties - Presentation - Display of Null-Values

HTH
Peter

[agsearle]

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.

[prieper]

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

[agsearle · Accepted solution]

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!