How can I identify null field(s) in data source?

[#1]

Dear QV community,

I’d be grateful if you could help me with the following issue.

I need to identify which field (or fields) are null in my data source.

Suppose I have to validate 03 fields

FLD1

FLD2

FLD3

For 03 fields, there are 07 possibilities

‘FLD1 is empty’

‘FLD2 is empty’

‘FLD3 is empty’

‘FLD1 and FLD2 are empty’

‘FLD2 and FLD3 are empty’

‘FLD1 and FLD3 are empty’

‘FLD1, FLD2 and FLD3 are empty’

For 03 fields only, I’d be rather simple to solve using a structure of nested if’s and isnull() function.

But unfortunately I have to validate ‘n’ fields, so I think nested if’s would be pretty complicated.

Is there any other structure available in QLIKVIEW to perform the validation above?

Thank you

Leonardo

[lawrenceiow]

I guess it depends on where you're going with this but is the Information Density feature of the Table Viewer not what you're after?

[m_woolf]

You might look at the SWITCH keyword in script.

[#4]

Thank you for your reply.

Given that more than one field can be null, I need to store in one field the resulting text that clearly depicts which fields are empty. Think it as a "data quality" check.

in the example:

‘FLD1 is empty’

‘FLD2 is empty’

‘FLD3 is empty’

‘FLD1 and FLD2 are empty’

‘FLD2 and FLD3 are empty’

‘FLD1 and FLD3 are empty’

‘FLD1, FLD2 and FLD3 are empty’

So I need to store in one field the text that describes which field(s) are empty.

[#5]

Yes. Switch will work for you. Below is the syntax.

1. SWITCH RESERVELOCATION 
2.      CASE 001  
3.          LOAD TRIM(PSAREA) & TRIM(PSZONE) & Left(TRIM(PSAISL),1) & Right(TRIM(PSAISL),1) & TRIM(PSBAY); 
4.      CASE 003 
5.          LOAD TRIM(PSAISL) & TRIM(PSLEVL) & TRIM(PSPOSN); 
6.      DEFAULT 
7.         LOAD TRIM(PSAISL) & TRIM(PSLEVL) & TRIM(PSPOSN); 
8.      END SWITCH; 

[lawrenceiow]

Apologies, I don't have the answer but could you clarify something ...

Are you seeking to find which RECORDS have empty FIELDS or which FIELDS over the entire dataset are empty?

So, for any given record you would like a field to inform you that Fields 1 and 4 (for example) are empty .

or

Over the entire dataset (table) you need to know that no records contain values in Fields 1 and 4 (for example).

[#7]

sorry I was not clear, my fault

It's the first situation: For any given record I would like a field to inform that fields 1 and 4 are empty.

[#8 · Accepted solution]

After you loaded your data, you could try something like this (using "base 2" strategy):

v_fld1 = IF(FieldIndex( 'FLD1', Null( ) ) <> 0, 1, 0);   // 1 = 2^0

v_fld2 = IF(FieldIndex( 'FLD2', Null( ) ) <> 0, 2, 0);   // 2 = 2^1

v_fld3 = IF(FieldIndex( 'FLD3', Null( ) ) <> 0, 4, 0);   // 4 = 2^2

v_fld4 = IF(FieldIndex( 'FLD4', Null( ) ) <> 0, 8, 0);   // 8 = 2^3

...

You would then just need to compare the value of the sum of all the v_fld* variables:

0 --> No null values exist

1 --> Only FLD1 has a null value

2.--> Only FLD2 has a null value

3 --> FLD1 and FLD2 have a null value

4 --> Only FLD3 has a null value

5.--> FLD1 and FLD3 have a null value

6 --> FLD2 and FLD3 have a null value

7 --> FLD1, FLD2, and FLD3 have a null value

8 --> Only FLD4 has a null value

9 --> FLD1 and FLD4 have a null value

etc etc

You can put this whole list into a SWITCH statement:

switch v_fld1 + v_fld2 + v_fld3 + v_fld4

case 0

     v_result = 'No null values exist';

case 2

     v_result = 'Only FLD1 has a null value';

....

end switch

Hope this helps.

[rwunderlich]

You can do it with Base2 flags as Ruben suggested, but here's what I think is an easier bit of script.

// Create some test data

NullMap:

Mapping LOAD 0,null() AutoGenerate 1;

MAP FLD1, FLD2, FLD3 USING NullMap;

data:

LOAD * INLINE [

FLD1, FLD2, FLD3

0,1,1

1,0,1

1,1,0

0,0,1

1,0,0

0,0,0

]

;

//Flag which fields have null

data2:

LOAD

  *,

  IsNull(FLD1) * -1 + IsNull(FLD2) * -2+  IsNull(FLD3) * -4 as Flag

Resident data

;

DROP TABLE data;

That will generate Flag values 1-7 corresponding to your seven possible states.

-Rob

http://masterssummit.com

http://robwunderlich.com

[#10]

Rob, thank you for your help. It is actually an elegant solution.