topic Re: How can I identify null field(s) in data source? in QlikView

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

Mon, 23 Feb 2015 16:46:47 GMT

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

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

lawrenceiow — Mon, 23 Feb 2015 17:07:40 GMT

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?

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

m_woolf — Mon, 23 Feb 2015 17:09:24 GMT

You might look at the SWITCH keyword in script.

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

Mon, 23 Feb 2015 17:26:49 GMT

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.

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

Mon, 23 Feb 2015 17:36:49 GMT

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

SWITCH RESERVELOCATION
CASE 001
LOAD TRIM(PSAREA) & TRIM(PSZONE) & Left(TRIM(PSAISL),1) & Right(TRIM(PSAISL),1) & TRIM(PSBAY);
CASE 003
LOAD TRIM(PSAISL) & TRIM(PSLEVL) & TRIM(PSPOSN);
DEFAULT
LOAD TRIM(PSAISL) & TRIM(PSLEVL) & TRIM(PSPOSN);
END SWITCH;

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

lawrenceiow — Mon, 23 Feb 2015 17:40:08 GMT

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 .

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

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

Mon, 23 Feb 2015 17:48:04 GMT

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.

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

Mon, 23 Feb 2015 18:24:26 GMT

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.

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

rwunderlich — Mon, 23 Feb 2015 19:20:47 GMT

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

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

Mon, 23 Feb 2015 19:30:35 GMT

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