Adding a number and a null value or Converting a null value into zero(0)

odk-collect
odk-xlsform
#1

Hello ODKers ,
In an XLSForm I want to add a null value and number . I have a sheet where first i want to ask about which type of employees are present i.e whether full time or part time employees in select multiple type question . If he selects full time then I will ask how many full time employees are there if he selects part time I will ask how many part time are there . In the next question i want total employees present , which is nothing but sum of full time and part time . I want to show a hint where i will add part time and full time and show him how many he has to fill in the total employees field. If both part time and full time are filled then that sum works if any one is not filled then the sum is "Nan" and there is no hint shown . I figured out the reason being the empty field when added with non empty field the sum is "Nan" . So i wanted to know whether i can change that empty field to 0 .

I tried by adding if condition the constraint section where it works great , but in an big organisation that will be a very tedious task . So I want to convert empty field to zero.

Thanks and Regards,
SGSC .

0 Likes

#2

Hi @SGSC,

Perhaps you can try replacing nullable items with if() constructs in your sum formula like
if(${NullableVariable1}="",'0', ${NullableVariable1}) + if(${NullableVariable2}="",'0', ${NullableVariable2}) + ...

2 Likes

#3

Hello @Souirji_Abdelghani ,
Thanks for your reply . Yes it will solve the problem and i have tried it but consider a government super speciality hospital , where there will be a lot number of doctors like different medicine specialists , surgery specialists , practitioners and so on . At the total field , adding that If condition of those large number of fields will be difficult , like if there is a small mistake finding that will be very difficult . Can we anywhere in the ODK form definition give any instruction to consider integer type question null values as zero ?.

Thanks and Regards,
SGSC.

0 Likes

#4

Hi @SGSC,

Did you try default '0' value for those variables that may be null?

1 Like

#5

Strictly speaking, this is an XPath 'feature', not an ODK one; XPath typecasts empty strings to NaN when you reference a (string) XML instance value in an arithmetic XPath expression. Basically, to accomplish what you desire, you have to explicitly check whether its empty and return 0, as Souirji suggested, ie

if(${foo}='',0,number(${foo}))

Having XPath in XForms is an awesome tool at times, but you sometimes have to live with its warts... :slight_smile:

3 Likes

#6

Thank you @Xiphware @Souirji_Abdelghani .:smile::smile:

0 Likes

#7

Hi,

Not sure if this helps, we have NVL in oracle to do this job, guess we have
an alternative in your language usage. Hope it helps.

Thanks,
Sneha

0 Likes

#8

Hello @Snehaniranjana ,
Thanks for your reply , I have seen about NVL and yes that will help at the database end , but i want at the user level who is working in offline mode(i.e he does not have the internet connection) . But , while dealing with the database this is a great solution.

Thanks and Regards,
SGSC.

0 Likes

#9

@Gokul Sai Chandan Seethiraju http://forum.opendatakit.org/u/sgsc,

Yeah I understand, we use that in PL/SQL as well. by the way what language
do you use? may be I can try to dig in and see if I may help.

Thanks,
Sneha

0 Likes

#10

Hi

I was having a similar issue with NaNs because of null values in some of my questions.

The suggestion to include a default response of zero worked really well!

Thanks

1 Like

#11

I am glad it helped!

0 Likes