I want to make two tables from the same data. In one table I only want to include records where one of the date fields is null and in the other I want to count records where the field is not null. Right now, I include:
=IF(IsNull([Date of Appointment]) , [Column Name])
in each column I want in the table. This means I need to paste this into each column and change the name. This can't be the best way to do this. What is the proper way?
I can think of two approaches.
1. Make a variable that looks like the above only you can insert column name into it, so something like:
=$(nullDateOfAppointment([Column Name]))
but I am unsure of the syntax or how to set it up.
2. Is there some sort of filter that I can add to the table itself?
You could use such parametrized variable as a customized function:
set v = "IF(IsNull([Date of Appointment]) , $1) as $1";
and then just calling:
load $(v([F1)), $(v([F2)), $(v([F3)) from ...
But I wouldn't do it - beside the above efforts there are then two tables which wouldn't produce much benefit rather the opposite. Therefore I would create a flag like:
-IsNull([Date of Appointment]) as FLAG
and then using this flag within where-clauses or within listbox or in a set analysis to differentiate between both states.
