Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Use Concatenate.
I need recommend you studying this useful function: