Display value only for Null department

[jeevays7]

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.

[PrashantSangle]

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

Regards,
Prashant

[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]

Hi Prashant,

it is not working.

[jeevays7]

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.