I have a dataset of Delay by Day by Category (and many other Fields). I have another table of DelayTarget by Month and Category.
I am currently associating the target table on Month & Category but when there is no Delay for a given Category in a given Month, then the DelayTarget value does not display in my dashboard.
How do I associate the DelayTarget to all Months in my main dataset - even when there is no Delay to report? I think I want to create a Zero value for Delay when it is null but I don't know how to do this or if this is the best method.
Try to Concatenate to the table DelayTarget something like this:
Concatenate ([table name that contains DelayTarget]
Load Distinct Month(Day) as Month
,Category
,0 as DelayTarget
Resident [table that contains Delay, Day, Category]
Where not(Exists(_Month&Category, Month(Day) & Category))
;
Also, create the new field in the table that contains Delay, Day and Category:
Month(Day) & Category as _Month&Category
The key words for your issue Concatenate and Exists
Thanks for the reply, but I can't get this to work.
My understanding of your proposed solution is that it would create 0 values for missing DelayTarget but I want the 0 value to replace missing Delay field values.
I've got as far as creating a cartesian product so I now have a table of all possible combinations of Day and Category with a 0 value for Delay. I just don't know how to join this to my facts table such that the missing Delay values are replaced.
Missing:
NoConcatenate
LOAD Distinct
Date as Missing.Day
resident facts;
join
LOAD Distinct
Category as Missing.Category,
0 As Missing.Delay
resident facts;
(for the record the Date field represents a complete list of all dates... just not for every Category)
