How do I create zeros for null values in line chart?
I'm importing a SQL query that data and have included a batch date transform at the workbench level. I've joined the SQL query to the Calendar dataset so that dates are created for any weeks missing data which I was hoping would give me a 0 line on my chart for those days, but its not working. I've done this successfully in two other cases, but the difference in those cases was that the imported data was from Salesforce.
I've tried this in beast mode but that gives me an error: An issue has occurred during processing. We are unable to complete the request at this time.
WHEN `System Name`='' or `System Name` LIKE '' then 0 ELSE COUNT(DISTINCT `System Name`) END)
I've also tried doing the count from the ETL and I'm able to get 0's to show up on the dataset, but not on the chart. Also, when I do it through the ETL, it takes hours to process due to the number of rows.
Currently, I'm trying to blend the calendar and SQL data matching the dt and 'batch date' columns. I'm just at a loss on what to do here. Any thoughts?