2 Replies Latest reply: Oct 17, 2018 5:30 AM by Juraj Misina RSS

    How to excl NULL value/data in measures when using CONCATENATE

    Peter Gustafsson

      Hello,

       

      I have a question on CONCATENATED data; (I am using data from two different databases (OLD & NEW db) with same tables & names - data is different of course)

      If I have understood the function correctly "Rows with like Key values are not merged together. The rows from Table2 are simply appended to Table1. Because the tables have different fields, rows will have null values for the fields from the "other" table."

       

      How to disregard all the NULL values in pivot or graphs so they dont affect the lay-out or expressions/calculation used in QlikSense?

      Is there a "quick fix" in the script coding or expressions to be used?

       

      Yes, I can use in some cases Untick the "Incl Null values" for a table (Row) but it doesnt work for calculated fields/data (Measures).

      I can also use "Select All" in selection menu as a workaround (then the data excl NULL data for instance if Order year is selected) but its not very good if you forget to do that...

       

      For instance to calculate number of components not excluded "X" in all orders, I get "double" the number of components which I suspect is because the concatenate function?

       

      =if((nsc_state) <> 'X',sum({<component_code -= {'XXX_YYY'}>} quantity),'0')

       

      Any ideas?