Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
May be like:
Load
*
From <> Where isnull(Header1) or isnull(Header2) or isnull(Header3).....;
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*))
You mean you are using 'Trim(Len(Header*))' in where clause? Is that really working?
It works, didn't write you all the condition
Trim(Len(Header*)) = 0
See, it is not working for me.
Trim(Len(Header1)) = 0 or Trim(Len(Header2)) = 0 or...
This should rather be like:
Len(trim(Header1)) = 0 or Len(trim(Header2)) = 0 or...
Note: trim() as inner function.
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;
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