How to convert fields with only a HYPHEN to a NUM 0?

[#1]

Dear all,

In my script, I am creating a new column "ReadPassed", based on the condition that two existing columns contain the values "TAKEN" (reference column 1) and "PASSED"  (reference column 2):

COUNT_READ_PASSED:
 
NoConcatenate
LOAD
     [Employee ID],
     TEXTCOUNT([Employee ID]) AS ReadPassed
     RESIDENT
     Final_table
WHERE [Test Status] = 'TAKEN' AND [Test Result] = 'PASSED'
GROUP BY [Employee ID];

The resulting column "ReadPassed" contains the values 1, 2, 3, etc., which is the number of Test per employee for which both conditions are fulfilled.

The problem is that for employees for whom there is no case in which both conditions are fulfilled, the resulting field contains only a hypen ("-").

How can I convert this hyphen to a numeric 0 value instead?

Thanks a lot!

[Sokkorn]

Hi sapwnn44,

Can you try this and let me know:

NumSum(TEXTCOUNT([Employee ID])) AS ReadPassed

Regards,

Sokkorn

[sivarajs]

If(Isnull(TEXTCOUNT([Employee ID])=-1,0,TEXTCOUNT([Employee ID])) as
ReadPassed

[er_mohit]

have you tried

if(textcount([Employee ID])='-' or ' ',0,textcount([Employee ID]) AS ReadPassed

[#5]

Dear Sokkorn,

I tried it, but I still get the hyphen in the same fields.

[#6]

Same here, unfortunately.

I tried it, but I still get the hyphen in the same fields.

[#7]

Same here, unfortunately.

I tried it, but I still get the hyphen in the same fields.

[Sokkorn]

Dear sapwnn44,

Try this one

Set NullValue=0;

COUNT_READ_PASSED:
NoConcatenate
LOAD
     [Employee ID],
     TEXTCOUNT([Employee ID]) AS ReadPassed
     RESIDENT
     Final_table
WHERE [Test Status] = 'TAKEN' AND [Test Result] = 'PASSED'
GROUP BY [Employee ID];

Set NullValue='<NULL>';

Regards,

Sokkorn

[alexandros17]

Try This

COUNT_READ_PASSED:

NoConcatenate

LOAD

     *,

     if(isnull(ReadPassed) or ReadPassed='-', 0, ReadPassed) as NewReadPassed;
LOAD
     [Employee ID],
     TEXTCOUNT([Employee ID]) AS ReadPassed
     RESIDENT
     Final_table
WHERE [Test Status] = 'TAKEN' AND [Test Result] = 'PASSED'
GROUP BY [Employee ID];