Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;
Thanks for the quick reply! I built off of your example using LEN() vs isnull() and got the results I needed. Cheers!
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
this is much more elegant than what I did:)