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

[tigra999]

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?

[teiswamsler]

Hi Peter

The quick fix chould be to Load Resident on the concatenated table, then use the ALT() function on the fields you wanna populate with zero.

/Teis

[juraj_misina]

Hi Peter,

you can try this:

- when loading data from tables 1 and 2, flag them in script:

Data:

LOAD

'T1' as Source,

*

from Table1;

Concatenate

LOAD

'T2' as Source,

*

from Table2;

Then in your measures you can filter for respective sources

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

Also, using "naked" field names ([nsc_state] in your case) is not a good practice, because this makes the calculation consider whole dataset (respecting selections), instead of only data really needed for the chart. This can also cause null values to appear. You can read more in this article: Use Aggregation Functions!‌

Hope this helps.

Juraj