Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

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

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 1Header 2Header 3Header 4Header 5Header 6
111111
22222
333333
444444
5

5

6

The result should be like:

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

5

6

Thank you!

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

9 Replies
tresesco
MVP
MVP

May be like:

Load

          *

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

MindaugasBacius
Partner - Specialist III
Partner - Specialist III
Author

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
MVP
MVP

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

MindaugasBacius
Partner - Specialist III
Partner - Specialist III
Author

It works, didn't write you all the condition

Trim(Len(Header*)) = 0

tresesco
MVP
MVP

See, it is not working for me.

Capture1.PNG

Capture.PNG

MindaugasBacius
Partner - Specialist III
Partner - Specialist III
Author

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

tresesco
MVP
MVP

This should rather be like:

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


Note: trim() as inner function.

sunny_talwar

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
Master II
Master II

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

Best Regards,
KC