Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there!
Can someone help me count null values in my Straight Table? Need to know what expressions to write if I want to count the total number of missing information of each transaction in my table.
Below is my example:
ID | A | B | C | D | MissingCount |
1 | a | b | null | null | 2 |
2 | null | b | c | d | 1 |
3 | a | null | null | null | 3 |
What would be my expression for the MissingCount? Your help is much appreciated. Thank you.
regards,
Bea
Set NullInterpret = '';
TestData:
NoConcatenate
LOAD * INLINE [
ID, A, B, C, D
1,a,b,,
2,,b,c,d
3,a,,,
];
//******************
// Variant 1
LEFT JOIN(TestData)
LOAD ID, if(IsNull(A),1,0)+if(IsNull(B),1,0)+if(IsNull(C),1,0)+if(IsNull(D),1,0) as MissingCount1
Resident TestData;
//******************
//******************
// Variant 2
CheckColumns:
NoConcatenate
LOAD * INLINE [
CheckColumns_Table, CheckColumns_Key, CheckColumns_Column
TestData,ID,A
TestData,ID,B
TestData,ID,C
TestData,ID,D
];
Tmp1:
NoConcatenate
LOAD Distinct CheckColumns_Table as Tmp1_Table, CheckColumns_Key as Tmp1_Key
Resident CheckColumns;
for vRow1 = 1 to NoOfRows('Tmp1')
LET vTable = peek('Tmp1_Table', $(vRow1)-1, 'Tmp1');
LET vKey = peek('Tmp1_Key', $(vRow1)-1, 'Tmp1');
Tmp2:
NoConcatenate
LOAD CheckColumns_Column as Tmp2_Column
RESIDENT CheckColumns;
LET vTextMissingCount2 = '';
FOR vRow2 = 1 to NoOfRows('Tmp2')
LET vCheckCol = peek('Tmp2_Column', $(vRow2)-1, 'Tmp2');
LET vTextMissingCount2 = vTextMissingCount2 & 'if(IsNull($(vCheckCol)),1,0)+';
NEXT vRow2
LET vTextMissingCount2 = Mid(vTextMissingCount2,1,Len(vTextMissingCount2)-1);
LEFT JOIN($(vTable))
LOAD $(vKey), $(vTextMissingCount2) as MissingCount2
Resident $(vTable);
DROP TABLE Tmp2;
NEXT vRow1
//******************
Thank you so much for the help! This one solve my problem.