Questions about approximate match of lookup table and calculation of hidden value.

I’d like to ask several questions about lookup table and calculation of hidden value.
I would like to use “approximate match” at Lookup Table.

There are 2 fields. One is Agemos, other one is WFA.
In Agemos field, there is first decimal place such as 24, 24.5, 25.5 …

I’d like to match this value and calculated hidden value of “Age in Months” value which is extracted by Date of Birth, Age in Months or Age in years information.
I already know how to calculate “Age in months” using “Date of Birth” and “Age in years”.
But I don’t know how to extract it using “Age in Months”.
Because we would like to enter “Age in Months” using decimal place.
i.e. If children age is 4 years and 10 months, we would like to enter 4.10.
I know that If I use following formula, I can extract it. But I cant distinguish between 4.1 and 4.10.
note: I’d like to extract hidden value of “Age in month” from 4.10 to 58 (i.e. 4*12+10).

Incorrect formula:
if(“Age in Months” - int(“Age in Months”)=0.10, int(“Age in Months”)+10, if(“Age in Months” - int(“Age in Months”)=0.11, int(“Age in Months”)+11, …,if(“Age in Months” - int(“Age in Months”)=0.1, int(“Age in Months”)+1)

Therefore, I’d like to use Right function as same as excel function.

ex) in Excel case:
=IF(INT(RIGHT(“Age in Months”,2))>=10,INT(“Age in Months”)+INT(RIGHT(“Age in Months”,2)),IF(INT(RIGHT(“Age in Months”,1))>=1,INT(“Age in Months”)+INT(RIGHT(“Age in Months”,1)),INT(“Age in Months”)))

But I couldn’t find out how to use Right function at following page.

Could you tell me whether CommCare support right function or not.
If commCare support it, please tell me how to use it?
Else, could you tell me whether there is way to extract this value?
And I also would like to clarify whether number/decimal question box at CommCare distinguish between 4.1 and 4.10 or not.

And after calculation of this hidden value of “Age in months”, I’d like to use lookup table using this value.
I know how to use lookup table, but I don’t understand how to use “approximate match”.
This hidden value of “Age in months” is integer value. On the other hand, lookup table is used by decimal place.

There is reference how to use approximate match at following page.

After watching it, I thought that I can extract it using a sign of inequality.
But it doesn’t work well.

My formula to extract this value
if(Agemos <= #form/group_question/calculated_age_in_months, max(Agemos), 0)

  • #form/group_question/calculated_age_in_months” is “calculated Age in months” using date of birth, Age in months(2-3 digit numbers) and Age in Year.

I’d like to extract only one approximate value, but it appear all of Agemos value less than #form/group_question/calculated_age_in_months.
If it is possible, I’d like to extract Agemos=24.5, when I entered “#form/group_question/calculated_age_in_months”=25.

I also tried int(Agemos), but it is not good for us.
Because I’m planning to introduce this HFA table as next steps.
Agemos of HFA table is 24, 24.5, 25, 25.5, 26…
If we can’t use an approximate match, we can’t extract of decimal point place.

In addition, it seems that int(Agemos) is not roundup.
int(Agemos=24.5) will be Agemos=24. Could you also tell me how to use roundup function in commCare application?

And for my next step, I’d like to enter default box automatically.
I think that there is only one choice, if I can use an approximate match at lookup table.
But I don’t want to check-in this lookup table every time.
Therefore, please tell me how to enter this value automatically.

I tried to set up default value of lookup table, but I can’t drug and drop from lookup table data to default value.
Please see the screenshot.png. It seems that I can drop, but I can’t drop it.


It sounds like the function you are looking for is substr(), does that accomplish the task you are describing?


Hi Clayton,

Thanks for your advice.
Yes, I’m looking for substr() function.

But I can’t get my expected result using substr function.
Because “Decimal question” can’t distinguish between 1.1 and 1.10.
When I put 1.10, it seems that this value change to 1.1 automatically.
Even if I used substr(Decimal question, 2,4) for Decimal question=1.10, expected value=10 was not appeared.

Is there any way to distinguish between 0.1 and 0.10 at Decimal number quesiton box?
I tried to check string-length function too. but 1.10 is counted as 3 at string-length function.

My formula is below.
#form/group_question/age_in_months2 is decimal number quesiton.

if(int(#form/group_question/age_in_months2) > 9 and substr(#form/group_question/age_in_months2, 3, 5) >= 10, int(#form/group_question/age_in_months2) * 12 + substr(#form/group_question/age_in_months2, 3, 5), if(int(#form/group_question/age_in_months2) > 9 and substr(#form/group_question/age_in_months2, 3, 4) >= 1, int(#form/group_question/age_in_months2) * 12 + substr(#form/group_question/age_in_months2, 3, 4), if(int(#form/group_question/age_in_months2) - #form/group_question/age_in_months2 = 0, int(#form/group_question/age_in_months2) * 12, if(substr(#form/group_question/age_in_months2, 2, 4) >= 10, int(#form/group_question/age_in_months2) * 12 + substr(#form/group_question/age_in_months2, 2, 4), int(#form/group_question/age_in_months2) * 12 + substr(#form/group_question/age_in_months2, 2, 3)))))

Hi Satoshi

I think this is probably cause the decimal question is a number type question in your form. perhaps you can use a text input, with a regex validation rule.

check this page out, it might help you out with this problem


Hi Mazz,

Thanks for your info.
I could distinguish between 0.1 and 0.10 using text input and regex validation rule.


On another note, make sure your users are ok with having to type in decimal 0's even if they don't need them.

Alternatively, perhaps you could use some fancy if statements and length functions to pad with 0's if you need to but it might be overkill :slight_smile:

Glad it works!