How to handle Null values in a report
19 November, 2013
Hi,
In is report with sub queries for each time period. I am having trouble generating the report if there is no data in the Master query - I need the master query to display a "0" if there is no data for that period ? At present if there is no data in the master query the report leaves the whole row out, even if there is data in the other time periods.
How does Yellowfin handle null values in a report?
Regards,
Nick
In is report with sub queries for each time period. I am having trouble generating the report if there is no data in the Master query - I need the master query to display a "0" if there is no data for that period ? At present if there is no data in the master query the report leaves the whole row out, even if there is data in the other time periods.
How does Yellowfin handle null values in a report?
Regards,
Nick
Hi Nick,
It sounds like what you're facing is related to the join itself.
If there is no data to join, then the sub-query will not return data.
You will need to use a 'full-outer join' for this type of report.
Keep in mind, that not all databases support all join types.
Please let us know how you go.
Regards,
David
It sounds like what you're facing is related to the join itself.
If there is no data to join, then the sub-query will not return data.
You will need to use a 'full-outer join' for this type of report.
Keep in mind, that not all databases support all join types.
Please let us know how you go.
Regards,
David
Hi David,
Thanks for the reply.
So if there is no data is there a populate the cell with a default value.
For example: If Null then "$ 0.00"
Best regards,
Nick
Thanks for the reply.
So if there is no data is there a populate the cell with a default value.
For example: If Null then "$ 0.00"
Best regards,
Nick
Hi Nick,
You should be able to get away with this by using a virtual table to hold your case statement. Then you can use these fields in your report ;).
Hope this helps.
Regards,
David
You should be able to get away with this by using a virtual table to hold your case statement. Then you can use these fields in your report ;).
Hope this helps.
Regards,
David
Hi David,
Could you please provide more information on how a virtual table would potentially help in this scenario?
Regards,
Nick
Could you please provide more information on how a virtual table would potentially help in this scenario?
Regards,
Nick
Hi Nick
There are 2 issues here
1. Rows missing if Master query has no values
Because a left outer join is used the Master query must contain all possible rows.
To display all possible results ensure that your master query ONLY contains the join fields, and the sub-queries contains the join fields and the values.
eg. Master Query contains Region ONLY but no amounts
This displays all the regions and only the amounts where there are values
2. Replace NULL values with "0"
You can use a CASE statement to convert the NULL values to 0.00
This will now show the NULL values as 0
Hope this solves your problem.
Regards,
Stephen
There are 2 issues here
1. Rows missing if Master query has no values
Because a left outer join is used the Master query must contain all possible rows.
To display all possible results ensure that your master query ONLY contains the join fields, and the sub-queries contains the join fields and the values.
eg. Master Query contains Region ONLY but no amounts
This displays all the regions and only the amounts where there are values
2. Replace NULL values with "0"
You can use a CASE statement to convert the NULL values to 0.00
This will now show the NULL values as 0
Hope this solves your problem.
Regards,
Stephen
Hi,
Is this also possible with a union sub query.
Regards,
Nick
Is this also possible with a union sub query.
Regards,
Nick
Hi Nick
The CASE statement will work even for a Union sub-query.
However Union sub-query's only returns rows where actual data exist for the join,
so it will only return NULL values if it exist in the database that way.
Regards,
Stephen
The CASE statement will work even for a Union sub-query.
However Union sub-query's only returns rows where actual data exist for the join,
so it will only return NULL values if it exist in the database that way.
Regards,
Stephen