Qlik Community

Third-Party Qlik Jobs

Area for people to learn more about current Qlik related job postings.

Highlighted
Partner
Partner

Display value only for Null department

Hi All,

I have to tables and am doing left join.

 table 1:

NameValue
A10
B20
C30
D40
E50
F60
G55
H75

 Table 2:

NameDept
ACSE
BEEE
CECE
EEEE
HCSE

 

when i am doing left join, not matching values are Null in the newly created column.

NameValueDept
A10CSE
B20EEE
C30ECE
D40-
E50EEE
F60-
G55-
H75CSE

 

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.

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
Partner

Re: Display value only for Null department

Hi Prashant,

it is not working.

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

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.