Condition. Rows where in any of the columns the field is empty

[MindaugasBacius]

Hei,

I would like to know what's the best practise to get only the rows where in any of the columns the field is empty?

There is a data table:

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6
1	1	1	1	1	1
	2	2	2	2	2
3	3	3	3	3	3
4	4	4	4	4	4
				5	5
					6

The result should be like:

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6
	2	2	2	2	2
				5	5
					6

Thank you!

[tresesco]

May be like:

Load

          *

From <>    Where isnull(Header1) or isnull(Header2) or isnull(Header3).....;

[MindaugasBacius]

Isn't there more intelligence way to do this?

Let's assume I have 100 fields?

As I practically doing the same with Trim(Len(Header*))

[tresesco]

You mean you are using 'Trim(Len(Header*))' in where clause? Is that really working?

[MindaugasBacius]

It works, didn't write you all the condition

Trim(Len(Header*)) = 0

[tresesco]

See, it is not working for me.

[MindaugasBacius]

Trim(Len(Header1)) = 0 or Trim(Len(Header2)) = 0 or...

[tresesco]

This should rather be like:

Len(trim(Header1)) = 0 or Len(trim(Header2)) = 0 or...

Note: trim() as inner function.

[sunny_talwar · Accepted solution]

May be one options would be to do a cross table and then back to the normal table if you don't want to type out all the field names in your where statement

Table:

CrossTable (Header, Value)

LOAD RowNo() as RowNum,

  [Header 1],

    [Header 2],

    [Header 3],

    [Header 4],

    [Header 5],

    [Header 6]

FROM

[https://community.qlik.com/thread/237966]

(html, codepage is 1252, embedded labels, table is @1);

Right Join (Table)

Load RowNum

Where Count < 6;

LOAD RowNum,

  Count(If(Len(Trim(Value)) > 0, Value)) as Count

Resident Table

Group By RowNum;

FinalTable:

LOAD RowNum

Resident Table;

For i = 1 to FieldValueCount('Header')

  Let vHeader = FieldValue('Header', $(i));

  Left Join (FinalTable)

  LOAD RowNum,

  Value as [$(vHeader)]

  Resident Table

  Where Header = '$(vHeader)';

NEXT

DROP Table Table;

[jyothish8807]

Hi Minddaugas,

Try like this. Hope it helps.

Tab:

LOAD [Header 1],

     [Header 2],

     [Header 3],

     [Header 4],

     [Header 5],

     [Header 6]

FROM

[https://community.qlik.com/thread/237966]

(html, codepage is 1252, embedded labels, table is @1);

Let vNooffields=NoOfFields('Tab');

for i=1 to $(vNooffields)

let vFieldname=FieldName($(i),'Tab');

Fieldname:

Load $(i) as ID,'$(vFieldname)' as Field

AutoGenerate 1;

next i;

Field:

Load

left(Concat('isnull(['& Field &']) or '),len(Concat('isnull(['& Field &']) or '))-3) as Fields

resident Fieldname;

Let vFieldNames = Peek('Fields', 0,'Fields');

DROP TABLES Field,Fieldname;

NoConcatenate

Final:

Load

*

Resident Tab

where 'vFieldNames';

drop table Tab

Regards

KC