You could replace NULL with a value within the script with something like:
if(len(trim(Field))=0, 0, Field)
Possible is also to use statements like "NullAsValue" and "NullValue".
If you need to use "Suppress NULL" within the chart-options a replacing with 0 isn't helpful and you need to manipulate it with something like 0.000000000000000001.
thank you for your effort.
I tried the if( IsNull(field name) =0, 'default value' , field name) as field name in the load script but it did not work for me.
Thank you for your effort.
You have to create those datas with the value 0.
For your example:
You have to create those missing dates => Check out how to create a master calendar in case you don't know to create the dates.
Then you give 0 as value to each date and then join for the dates that doen't exist in your datas.
Here is an example:
LOAD * INLINE [
min(Date) as minDate,
max(Date) as maxDate
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
TempDate as Date
0 as Value
I gave the example only as an example.
I do not wish to create the dates manually since I have many dates-hole in the database which are varied for different cases.
Let say you collect money, from different countries and in each country there are different cities and in every city there are a few bank. You collect money from each bank but sometimes (some dates) a bank does not deposit money, does not give you money that date.
Now you want to show in a table the money deposit (aggregation expression) by country, by city or by a specific bank\s.
And when I pick a specific bank, when there is no deposit in some days, I want:
A.To present the missing days in my X line (time line)
B. And in those missing days to show zero values.