Third-Party Qlik Jobs

Highlighted
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.

Labels (1)

5 Replies

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

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.

Partner

Re: Display value only for Null department

Hi Prashant,

it is not working.

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

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.

Community Browser