PQL for checking if date is null
Trying to find the correct syntax for checking if a date is null in a script formulate. Currently accessing the date via [pyramid_custom_query_t_placement_patient_hsptl].[cl_effective_from_date].CurrentMember. What would a null check for this field include? Wrapping in IfNull(date,1) fails because the date is null.
4 replies
-
Hi Paul,
When Pyramid constructs its Semantic Model, each attribute / hierarchy that results is a list of unique values from the originating column. In the case of dates, if it encounters a null date in the column it assigns it a value of "--".
Here's some data, with one of the records missing a date value:
Building a model form this data we can see the Member representing null as "--":
Se we'd need to test the current member caption for the value "--":
Resulting in:
Hope that helps.
Ian
-
Thanks Ian Macdonald . On a similar note, we're also trying to get a count of IDs where 2 different date columns are between the user's selected end date. Would you know how to alter the piece within the red box so that it would check every row even if the ID isn't included on the visual? The commented out row works fine if the ID is included in the visual (presumably because the current member is then known and obvious), but would like the count without the ID present.
-
Hi Paul,
There is a clue in your question, "it would check every row", that leads me to think this is not a semantic calculation, but a row / table calculation. I suspect your previous question was as well.
In which case there are a number of options:
- If you are running Pyramid 2023, then the new feature, "Custom Columns", allows you to define row level calculations directly from Discover and/or Formulate using PQL
- If you are not running Pyramid 2023, and this is a Model built using Pyramid and utlising Model Data Flows, you can perform the calculation there using a "Calculation Block" in the Data Flow.
- If you are not running Pyramid 2023 and are using a Direct Query model into an existing database, then you will need to create a custom SQL query as part of the model to perform the calculation.
Let me know which it is and we can progress from there.
Ian