Measure in pivot table causing null value in dimension
Hello,
I have a pivot table in the front end with one dimension (response flag), and one measure(received responses).
The dimension comes from table A, and the measure comes from table B. These two tables are linked together in the DM via a LinkTable.
The dimension in use only has two possible results: 'Yes' or 'No'. There is absolutely no null entries in this dimension. The data cube in which this field comes from has already been configured to deal with any empty/null entries and assign those as 'No'. So in the end, all values have an associated flag connected to it.
When I add this field into the table in my data model (without any measure), then I only get two rows, one for yes, and another for no.
response flag (dimension) ='1' (temporary measure)
Yes 1
No 1
However, once I include my measure (received responses) into the table, a third line for the dimension is generated: null.
response flag received responses
Yes 4,578
No 2,976
- 152
Because a certain amount in the measure does not correlate to either flag value, the null appears in the table.
I need to replace this null with 'Unknown' since leaving the null line in here might cause confusion.
I've tried several functions via set analysis (ALT, LEN TRIM, NOT ISNULL etc), and even adjusted the field in the table itself in the DM script in the backed, but none of them worked. I always either get the same result or a skewed version of the existing version. Because the null isn't coming directly from the dimension itself, I cannot alter the field directly. The only reason why the null exists in this table, is because of the third chunk of the measure that has no flag value connected to it. In the end, I want to achieve this output:
response flag received responses
Yes 4,578
No 2,976
Unknown 152
I don't want the null line removed, I just want it renamed to 'Unknown'. And the values need to stay as is. Does anyone have any idea or suggestion as to how I can achieve this?
you cannot alter not existing value (null()) with other value.
Instead you need to create relationship with all missing/not associated values using qlik script by creating records which dont correlate to yes/no and by asigning it with unknown value.
cheers
Lech
cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
