topic Re: Dimension value missing show its expression as NA in QlikView

Dimension value missing show its expression as NA

Fri, 25 Sep 2015 14:41:39 GMT

There is a dimension having text values, a specific should be counted. It is counted using using substringcount but I want it to be case insensitive, 1) so do you have other workaround?

2) count is showing 0 where this dimension value is missing, I want it to show as NA?

Re: Dimension value missing show its expression as NA

marcus_sommer — Fri, 25 Sep 2015 14:58:29 GMT

1. you could use upper/lower for this: substringcount(Upper(YourField), 'X')

2. use a simple if(expression = 0, 'NA', expression)

- Marcus

Re: Dimension value missing show its expression as NA

Fri, 25 Sep 2015 15:28:28 GMT

There is a case where expression is zero and it should be 0 only.I am talking about case where there is no field value( field value missing). Does upper/lower do case insensitive operation. Count should consider all words like Apple, apple, APPLE, AppLe etc.,

Re: Dimension value missing show its expression as NA

marcus_sommer — Fri, 25 Sep 2015 15:52:18 GMT

If you have a dimension-value which will be displayed and no transaction-values for them each matching/calculation will be return NULL. This could be tried to fetched with a expression like: if(transactionField, expression, 'NA') or easier within the object-properties in tab presentation within the bottom left various options for NULL and missing values. But if there are no dimension- and transaction-values you couldn't display it - you would need to create those missing values within the script.

Substringcount is further case-sensitive but with lower/upper you converted the value first and could then compare against a single-case. In your example upper(Field) = 'APPLE' would match all of them "Apple, apple, APPLE, AppLe".

- Marcus