Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Display value only for Null department

Hi All,

I have to tables and am doing left join.

 table 1:

Name Value
A 10
B 20
C 30
D 40
E 50
F 60
G 55
H 75

 Table 2:

Name Dept
A CSE
B EEE
C ECE
E EEE
H CSE

 

when i am doing left join, not matching values are Null in the newly created column.

Name Value Dept
A 10 CSE
B 20 EEE
C 30 ECE
D 40 -
E 50 EEE
F 60 -
G 55 -
H 75 CSE

 

now i need to show total values of Null Dept in the above table.

How can i write set analysis? : Sum({$<Dept={""}>}Value) is not working.

How can i achieve this? the above scenario occurs in many places in my model.

give me the best solution please.

Tags (2)
Labels (1)
5 Replies
Highlighted

Re: Display value only for Null department

try below two expression
1: Sum({<Dept=>-<Dept=p(Dept)>}Value)
2: Sum(Total Value)- Sum({<Dept=p(Dept)>}Value)

Regards,
Prashant
Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted

Re: Display value only for Null department

You can try this

Sum({<Name = {"=Len(Trim(Dept)) = 0"}>}Value)

But this is assuming that a Name will only belong to one and only one Dept.

Highlighted
Partner
Partner

Re: Display value only for Null department

Hi Prashant,

it is not working.

Highlighted
Partner
Partner

Re: Display value only for Null department

Hi Sunny,

It is working, but how can i write a condition like: i need B and NULL dept values.
Sum({$<Dept={"",'B'}>}Value)
if Name column has more than one same name, how to do?
Highlighted

Re: Display value only for Null department

Create a flag in the script to do this

If(Dept = 'B' or Len(Trim(Dept)) = 0, 1, 0) as DeptFlag

This will be done after the two tables have been joined.