Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I need some help.
I have two dimensions. I can not fix this problem in the editor because the data is too large.
I need to fix this in the expression.
I have done a lot of research but maybe it is something small I am doing wrong.
Temp tale 1 and temp table 2
If the customer field is empty in temp1, then fill it with temp table 2 customer value
But it does not work.
My expression: if(IsNull(Name1),Name2,Name1)
IsNull() might not catch all cases of empty or whitespace only strings, so perhaps
If(not Len(Trim(Name1)), Name2, Name1)
Are you using this expression as a Measure or as a Dimension?
From the screen I can imagine that you’re using it as a measure which means you need to add aggregation functions, try Concat(distinct If(not Len(Trim(Name1)), Name2, Name1)) or Only(If(not Len(Trim(Name1)), Name2, Name1))
Hi Roman,
It look likes as soon as there is a null value, Qlik does not want to work.
I have tried all of the suggestions but I can not get rid of the null values.
It replaces the " - " with an empty space now
Kind regards
Theresa
Idk what's wrong on your side, see the example:
Tab1:
Load Null() as Name1,
123 as innvoice_key
Autogenerate 1;
Tab1:
Load 'Name1' as Name1,
124 as innvoice_key
Autogenerate 1;
Tab2:
Load 'SomeName1' as Name2,
123 as innvoice_key
Autogenerate 1;
Tab2:
Load 'SomeName2' as Name2,
124 as innvoice_key
Autogenerate 1;
exit script;
Thank you for the feedback. Just a bit of background. I work with mil of records.
I can not do this in the editor. I have two different dimensions, temp table 1 and temp table2 . Sometimes the customer name is empty in temp table 2, then I would like to use customer name from temp table1.
But as soon as Qlik sees a null value it will not look at the other field. Does it make sense?
I've replicated your case with 2 tables and null() value and replaced Null() value in Table1 with SomeName1 from Table2. So far I see no difference between the example and your case.
If it has replaced " - " with an empty space in your case, it means that in this formula
Concat(distinct If(not Len(Trim(Name1)), Name2, Name1))
both Name1 and Name2 are missing or there is some mistake
The expression itself may not be sufficient as everything depends on your data model and associations between data tables. If the null is because of lack of association expression will not be sufficient.
I also dont understand statement that you need to do this in UI because your data is too big - usually that argument would be oposite - you want to do this in script so your app can perform much better given its large data volume.
Also if you're 100% percent sure you need to replace only Null() values, you can go with this formula
Concat(distinct coalesce(Name1,Name2))
Where Name1 is your original name, Name2 is a name to replace if Name1=Null().