How to count null values (per rows) from a Straight Table?

[#1]

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

[#2]

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

//******************

[#3]

Thank you so much for the help! This one solve my problem.