Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
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.
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