Announcements
cancel
Showing results for
Did you mean:
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)
• ### Set Analysis

5 Replies
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 🙂
MVP

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.

Partner - Creator III
Author

Hi Prashant,

it is not working.

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

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.

Tags
Community Browser