Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All -
Need to select "Null" values in chart, these null values generated while connecting/relation between tables.
"NullAsValue" will useful while dealing with nulls in single load/table however my scenario is different. Is there any possibility ?
For instance: Have to select below highlighted records and see related information. Please consider this one as sample scenario.
Source:
Thank you.
How about this?
Tab1:
Mapping LOAD * Inline [
ID, Product
1, A
4, B
];
Tab2:
LOAD *,
ApplyMap('Tab1',ID,'<NULL VALUE>') as Product
Inline [
ID, Sales
1, 100
2, 200
3, 300
4, 400
];
EXIT SCRIPT;
Thanks for response. Actually my requirement is different. They are null values while having relation between multiple tables. I shared source for sample. There are multiple table and fields has null values.
NULL means the absence of any value and isn't stored anywhere and couldn't be accessed or selected - regardless if the NULL occurred directly within a table or if it's the result of an association. The only way to access/select NULL is to replace them with real values. The suggestion from @BrunPierre demonstrate an easy and very common way to handle such a scenario - and yes it means also that you need data(-quality) checks during the app-development and appropriate measures to adjust the data-model.
Usually it's neither needed nor sensible to replace all NULL's in all fields and all possible associations - else it may helpful by a few cases and therefore the extra efforts to handle them aren't really expensive ...
One other potential workaround for cases where this is specifically required is to use a calculated dimension, e.g.
Coalesce(Product,'-')
Which would make this dimension available for selection based on the context of the table, without modifying the underlying value of Product for the entire app.
Hello nihhalmca,
If you want to filter records in the chart, you have to add flag fields in the script as below:
Tab1:
LOAD *, 1 as flag1 INLINE [
ID, Product
1, A
4, B
];
Tab2:
LOAD *, 1 as flag2 INLINE [
ID, Sales
1, 100
2, 200
3, 300
4, 400
];
Then you can filter in the chart by appying filter in ID field as below:
=only({<flag2={1}>-<flag1={1}>} ID)
I hope it resolves you issue.
Hi,
While loading data you can check nulls see below post
https://data-flair.training/blogs/qlik-sense-null-functions/
If(len(trim(Product))= 0 or Value='NULL' or Value='-', Null(), Value ) as Prod_Null;
use this flags in set analysis to avoid null values
Vikas
@nihhalmca Please see the code used below:
NoConcatenate
Temp:
Load * inline [
ID, Product
1,A
2,B
];
Outer join (Temp)
Temp2:
Load * Inline [
ID, Sales
1, 100
2, 200
3, 300
4, 400
];
NoConcatenate
Temp3:
Load ID,
if(isnull(Product),'NULL',Product) as Product,
Sales
Resident Temp;
Drop table Temp;
Exit Script;
If this resolves your issue please like and accept it as a solution