Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

How can I add a column to a table that will display the fields that are blank, even if there are multiple blank fields?

I have a dashboard that displays the number of installations that were completed with missing data fields

I have created a table that displays:

-The installations that had blank fields

-The employee that missed entering certain data

-The barcode of the equipment that was installed with missing data

I would like to add a column that displays WHICH data field(s) was/were left blank. I have been using this expression:

if([ManufSerialNo.]='','Serial No.',if([Manufacturer]='','Manufacturer',if([Pole Treatment]='','Pole Treatment',if([Pole Species]='','Pole Species'))))

The issue is that sometimes there were installations completed with multiple data fields that were left blank (for example, both Pole Species and Pole Treatment were blank).

I have tried adding this formula to the above formula if([Pole Species]='' and [Pole Treatment] ='', 'Pole Species, Pole Treatment'), but it still only returns one of the fields.

Is there a way I can add a column that will display ALL the data fields that were left blank?

1 Solution

Accepted Solutions
Nicole-Smith
Not applicable

Re: How can I add a column to a table that will display the fields that are blank, even if there are multiple blank fields?

This should do the trick:

left(

  if([ManufSerialNo.]='','Serial No., ') &

  if([Manufacturer]='','Manufacturer, ') &

  if([Pole Treatment]='','Pole Treatment, ') &

  if([Pole Species]='','Pole Species, '),

  len(

       if([ManufSerialNo.]='','Serial No., ') &

       if([Manufacturer]='','Manufacturer, ') &

       if([Pole Treatment]='','Pole Treatment, ') &

       if([Pole Species]='','Pole Species, ')

  ) - 2

)

I've also attached an example file.

1 Reply
Nicole-Smith
Not applicable

Re: How can I add a column to a table that will display the fields that are blank, even if there are multiple blank fields?

This should do the trick:

left(

  if([ManufSerialNo.]='','Serial No., ') &

  if([Manufacturer]='','Manufacturer, ') &

  if([Pole Treatment]='','Pole Treatment, ') &

  if([Pole Species]='','Pole Species, '),

  len(

       if([ManufSerialNo.]='','Serial No., ') &

       if([Manufacturer]='','Manufacturer, ') &

       if([Pole Treatment]='','Pole Treatment, ') &

       if([Pole Species]='','Pole Species, ')

  ) - 2

)

I've also attached an example file.