topic Re: Target missing where no actual value in QlikView

Target missing where no actual value

stuhaigh — Sat, 16 Nov 2024 21:30:41 GMT

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.

Re: Target missing where no actual value

MindaugasBacius — Wed, 30 Jan 2019 05:39:16 GMT

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

Re: Target missing where no actual value

stuhaigh — Wed, 06 Feb 2019 15:38:24 GMT

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)

Re: Target missing where no actual value

MindaugasBacius — Thu, 07 Feb 2019 05:32:37 GMT

Use Concatenate.

I need recommend you studying this useful function:

https://community.qlik.com/t5/QlikView-Creating-Analytics/Understanding-Join-Keep-and-Concatenate/td-p/328379

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/ScriptPrefixes/concatenate.htm