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

Sum missing data fields if one of four fields are blank?

I have written the following if function in the data load editor:

LOAD

     *,

    if((ManufSerialNo='' OR Manufacturer='' OR ConstructYear='' OR "Valid From"=''),1,0)as BlankEquipmentField,

    if(ManufSerialNo='',1,0)as BlankManufSerialNo,

    if(Manufacturer='',1,0) as BlankManufacturer,

    if(ConstructYear='',1,0) as BlankConstructYear,

    if("Valid From"='',1,0) as BlankInstallationDate;

I would like it to count as "1" if any of the four data fields are left blank; however when I sum the data in Qlik Sense I am not getting the correct result as shown in the screen shot attached. I.e. The field with the most blanks should return the same total as the BlankEquipmentField

3 Replies
MVP
MVP

Re: Sum missing data fields if one of four fields are blank?

Why do you think the numbers should be the same? If two fields show equal amount of blank values, but in alternate rows, the total would be double the number, right?

Not applicable

Re: Sum missing data fields if one of four fields are blank?

Because I would like it to be that if any one of the four data fields (i.e. ManufSerialNo, Manufacturer, ConstructYear and Valid From) are blank it count as 1

So the total blanks should then be equal to the highest count of blanks between those four

MVP
MVP

Re: Sum missing data fields if one of four fields are blank?

Sorry, I don't understand why you expect the total to be equal the highest count of any of the OR'ed fields.

For example

A     B

1     1

1     0

0     1

0     1

If you create a new field C that is created like

A or B as C

the count of C equal 1 is 4, right? and the highest count would be 3.