Skip to main content
Announcements
Discover what’s possible with embedded analytics! March 6, 10 AM ET SIGN UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
jeevays7
Partner - Creator III
Partner - Creator III

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.

Labels (1)
5 Replies
PrashantSangle

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.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
sunny_talwar

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.

jeevays7
Partner - Creator III
Partner - Creator III
Author

Hi Prashant,

it is not working.

jeevays7
Partner - Creator III
Partner - Creator III
Author

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?
sunny_talwar

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.