Function for Counting Number of Fields Per Row That Have String

[pbrenneise]

Is there a way in Qlik to count the number of fields per row, which contain string? I have a large data set with a total of 9 columns that looks like this:

Col A	Col B	Col C	Col D	Col E	Col F	Col G	Col H	Col I
Entry 1	Y	N		Y	N	Maine	X	12/21/2015
Entry 2		N	Y	N	Y	Georgia	Y
Entry 3	Y	Y	N	N	Y	Alabama	Y	12/22/2018
Entry 4						Vermont		12/25/2017

...

The data were receiving is only partially complete and we'd like to calculate how many rows are missing data. Specifically, I need to add a new column in the Load Script, which contains a total based on how many fields from Col B Through Col I are LEN() > 1 for that particular row. Is there an efficient way to do this? Thanks!

[y_grynechko · Accepted solution]

Temp:
LOAD
"Col A",
if(isnull([Col A]), 1,0) as [Col A Nulls],
"Col B",
if(isnull([Col B]), 1,0) as [Col B Nulls],
"Col C",
if(isnull([Col C]), 1,0) as [Col C Nulls],
"Col D",
if(isnull([Col D]), 1,0) as [Col D Nulls],
"Col E",
if(isnull([Col E]), 1,0) as [Col E Nulls],
"Col F",
if(isnull([Col F]), 1,0) as [Col F Nulls],
"Col G",
if(isnull([Col G]), 1,0) as [Col G Nulls],
"Col H",
if(isnull([Col H]), 1,0) as [Col H Nulls],
"Col I",
if(isnull([Col I]), 1,0) as [Col I Nulls]
FROM [lib://Desktop/Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);

Table:
LOAD
"Col A",
"Col B",
"Col C",
"Col D",
"Col E",
"Col F",
"Col G",
"Col H",
"Col I",
[Col A Nulls]+ [Col B Nulls] + [Col C Nulls] + [Col D Nulls] + [Col E Nulls] + [Col F Nulls] + [Col G Nulls] + [Col H Nulls] + [Col I Nulls] as [Count of Nulls]
Resident Temp;
Drop Table Temp;

[pbrenneise]

Thanks for the quick reply! I built off of your example using LEN() vs isnull() and got the results I needed. Cheers!

[rwunderlich]

You could generalize the solution a bit like this:

Data:
LOAD
RecNo() as RecId,
"Col A",
"Col B",
"Col C",
"Col D",
"Col E",
"Col F",
"Col G",
"Col H",
"Col I"
FROM [lib://Data]
(html, utf8, embedded labels, table is @1);

ByCol:
CrossTable (ColName, ColValue, 1)
LOAD *
Resident Data
;
Join (Data)
LOAD
RecId,
-sum(len(ColValue)>0) as DataCount
Resident ByCol
Group By RecId
;

Drop Table ByCol;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[y_grynechko]

this is much more elegant than what I did:)