For example, suppose you are using a lookup table to set up a question with filtered choices. Your lookup table, health_centers_by_region, has the following fields:

• district_id

• health_center_id

• mnch_services_available

and you have a hidden value that contains the calculation:

• count(instance('health_centers_by_region')/health_centers_by_region_list/health_centers_by_region[mnch_services_available="yes"][district_id = /data/district_id])

If this table is large enough that this filter is slowing down performance in your form, you may be able to alleviate this by adding an index to district_id and moving the indexed field to the front of your filter, so that:

• [mnch_services_available="yes"][district_id = /data/district_id]

becomes:

• [district_id = /data/district_id][mnch_services_available="yes"]

Definitions

• A query refers to any XPath expression that retrieves data from a lookup table. This includes filters on lookup table questions, as well as any calculations that contain something of the form: instance('table_name')/table_name_list/table_name[filter_1][filter_2]...[filter_n]

• The cardinality of a field in a lookup table is how many unique values of that property are represented. The more unique values are represented in the table, the higher that field's cardinality. For example, an ID field for which each value only appears once in the lookup table has very high cardinality, while a True/False field has only two possible values over the entire lookup table, so has very low cardinality.

What is an Index?

An index is a data structure that helps make certain XPath expressions that reference a lookup table more efficient. When you index a field in a lookup table, you can improve the speed of expressions on that particular field. The following scenarios highlight the difference between querying a field that is indexed, compared to querying an unindexed field:

• Example Query: instance('state')/state_list/state[name="Bihar"]/population

• name is not indexed: The query evaluator loads a row of the state table, checks to see whether its name property is equal to "Bihar," returns the population field if so. Repeat for every single row of the table.

• name is indexed: The query evaluator checks the index to find the exact location in the table of all elements where name is equal to "Bihar," loads all matching elements in one operation, returns their population fields.

When Should I Create an Index on my Lookup Table?

Most lookup tables will not need to be indexed. If you think you might need to index one or more fields of a lookup table, ask yourself:

• Is my lookup table very large (many hundreds of records or more)?

• Do I query the same few fields over and over again in my app?

• Have I noticed slow app performance related to these queries?

• Do any of these fields have a high cardinality?

If you answered "yes" to all of the above questions, it might be worth adding an index for one or more fields in your lookup table. If you're not sure whether a particular query is slowing down your app, try the following exercise:

1. Create a form consisting of 3 questions:

   1. label1

   2. lookup_query

   3. label2

2. label1 and label2 are label questions, and can contain whatever you want.

3. lookup_query is a calculation question, whose calculate condition is set to the exact query that you believe might be causing the slowdown.

   1. If your query contains references to question_ids in the form whose values are dynamically generated, replace these with a static representative value. For example, in the query from the Overview, we can select an arbitrary district_id (e.g. "123") to use in place of /data/district_id.

   2. If you are concerned about a filter on a multiple choice question, your query is going to be: instance('table_name')/table_name_list/table_name[your filter here], where table_name is the name of the lookup table. In the example from the overview, our query would be: instance('health_centers_by_region')/health_centers_by_region_list/health_centers_by_region[district_id="123"][mnch_services_available="yes"]

Indexing Tables Used in Multiple Choice Lookup Table Questions

If you are populating a multiple choice or checkbox question from a lookup table, and are still experiencing performance issues after indexing any appropriate columns from the filter, try adding indexes for the Value Field and the Display Text Field.

How Do I Add an Index?

Suppose you have a lookup table health_centers_by_region with the following fields (this is the same table from the Overview above):

• district_id

• health_center_id

• mnch_services_available

As part of your workflow, a mobile worker often needs to select a health center in a certain region that has MNCH services available. There are thousands of health centers spread over hundreds of districts, and you've noticed that a query that appears throughout the app is taking a long time:

• instance('health_centers_by_region')/health_centers_by_region_list/health_centers_by_region[mnch_services_available = "yes"][region_id=/data/region_id]

Since district_id has hundreds of values represented, it has a high enough cardinality to warrant an index. mnch_services_available will only ever be set to "yes" or "no." Since the cardinality for this field is low, the benefits of indexing the field would be minimal compared to the extra space it would take up on the phone, so it should not be indexed.

Before adding an index to the lookup table, please refamiliarize yourself with the process of creating and updating lookup tables, specifically downloading or uploading tables for editing.

When you're ready to add an index, follow these steps:

1. Download the lookup table following the steps in the previously linked documentation.

2. Open the file and navigate to the types tab. It should look something like this: 

3. Add a column with header "field 1 : is_indexed?" and mark the column as "yes" for the field and table that you want to index:

Once the index has been added, you will need to rewrite your query so that the indexed field or fields appear at the beginning of the string of filters. Since region_id is indexed and mnch_services_available is not, our example becomes:

• instance('health_centers_by_region')/health_centers_by_region_list/health_centers_by_region[region_id=/data/region_id][mnch_services_available = "yes"]

See the next section for details.

Query Optimization

When writing XPath queries on an indexed lookup table (or on any indexed fixture, such as casedb), it is important to write your query such that all filters on indexed properties are evaluated BEFORE other filters. For example, say you have a lookup table health_centers_by_region with the following fields:

• region_id (indexed)

• health_center_id

• mnch_services_available

If we want a query to return all health centers in region 123 where MNCH services are available, we would write:

• instance('health_centers_by_region')/health_centers_by_region_list/health_centers_by_region[region_id="123"][mnch_services_available = "yes"]

With this query, the XPath query evaluator will first use an efficient key lookup to filter on the indexed field (region_id), before performing single lookups on each of the results to check whether mnch_services_available = "yes" for each one.

The following documents go into much further technical detail on XPath evaluation and optimization:

• https://github.com/dimagi/commcare-core/wiki/Primer:-XPath-Query-Evaluation

• https://github.com/dimagi/commcare-core/wiki/Performance:-XPath-Query-Evaluation

Limitations

Complex Queries

Currently, only the most basic XPath queries will leverage lookup table indexing. For example: value = 1 will use the indexing, but value < 2 will not. In addition, composite queries using and or or will not leverage the indexes.

Lookup Tables with Multiple Languages

When indexing a lookup table with multiple languages, you can only index fields that do not have translations. Indexing columns with attributes is unsupported.Managing large lookup tables efficiently

Sometimes when creating Lookup Tables, you can receive large source files with values in different fonts, all caps etc. This page describes a few tricks on how to make values easily all lower case, how to replace spaces by underscores etc. Below are a set of Excel formulas, with a description of what it does and how to use it.

Note: this tutorial assumes that you are using Excel in English. The names of the formulae are different in French.

How to turn all your text entries to first-letter capital for each word (ex: First_name Last_name)

=proper

Use: Converts words written in any format to words where the first letter is upper case and the rest is lower case. Example: CAPE TOWN > Cape Town

How: Add a column next to the column where all the values are, then in the first cell of the new column add =proper(cell number) and click enter (for 'cell number' click on the cell with the all caps value). Drag the corner right little box from the cell down so that all values from the all caps column will be converted.

When clicking on the new value, you'll notice that the value of the cell is still the formula. Select the first cell and then click ctrl+shift+down arrow (this will select all values in this column) and click ctrl+c to copy the selection. Then right click and choose 'paste values' under paste options. This eliminates the formula so that you are only left with the values you want (i.e., the text in the desired format).

Image Added

Note: For words that are already properly formatted (ex: Johannesburg in the example above), no change is applied. Note as well that "cape town" turns to Cape Town.

How to quickly format all your text entries to all caps:

=upper

Use: Converts words written in any format to all caps. Example: Cape Town > CAPE TOWN

How: Add a column next to the original words/text. Use the formula =UPPER(cell number) in the new column and click enter. Drag down the formula to replicate this for all your text. 

When clicking on the new value, you'll notice that the value of the cell is still the formula. Select the first cell and then click ctrl+shift+down arrow (this will select all values in this column) and click ctrl+c to copy the selection. Then right click and choose 'paste values' under paste options. This eliminates the formula so that you are only left with the values you want (i.e., the text in the desired format).

Image Added

How to quickly format all your text entries to lower case:

=lower

Use: Converts words written in any format to all lower case. Example: Cindy Jones > cindy jones

How: Add a column next to the original words/text. Use the formula =LOWER(cell number) in the new column and click enter. Drag down the formula to replicate this for all your text. 

When clicking on the new value, you'll notice that the value of the cell is still the formula. Select the first cell and then click ctrl+shift+down arrow (this will select all values in this column) and click ctrl+c to copy the selection. Then right click and choose 'paste values' under paste options. This eliminates the formula so that you are only left with the values you want (i.e., the text in the desired format).

Image Added

How to quickly delete unwanted spaces:

=trim

Use: For when you receive a list of names/places where there is a space before the first word or after the final word. Example " cindy jones " (notice the space before 'cindy' and after 'jones'. The TRIM function will yield "cindy jones"

How: Add a column next to the original words/text. Use the formula =TRIM(cell number) in the new column and click enter. Drag down the formula to replicate this for all your text. 

When clicking on the new value, you'll notice that the value of the cell is still the formula. Select the first cell and then click ctrl+shift+down arrow (this will select all values in this column) and click ctrl+c to copy the selection. Then right click and choose 'paste values' under paste options. This eliminates the formula so that you are only left with the values you want (ie, the text in the desired format).

Note: TRIM only deletes spaces before or at the end a full string of text. It will not eliminate common/accurate spaces such as the space between a first and last name.

Image Added

Combining the above formulas: (ex: to eliminate unnecessary spaces AND to format upper/lower case)

=UPPER(TRIM(cell number)) or =LOWER(TRIM(cell number)) or =PROPER(TRIM(cell number))

Use: Do the steps above more quickly!

How: Add a column next to the original words/text. Use the formula =UPPER(TRIM(cell number)) in the new column and click enter. Drag down the formula to replicate this for all your text. 

*the bolded UPPER can be modified to reflect what you actually want.

When clicking on the new value, you'll notice that the value of the cell is still the formula. Select the first cell and then click ctrl+shift+down arrow (this will select all values in this column) and click ctrl+c to copy the selection. Then right click and choose 'paste values' under paste options. This eliminates the formula so that you are only left with the values you want (ie, the text in the desired format).

Image Added

How to turn spaces into underscore (necessary for uploading your lookup tables into CommCare HQ)

Use: It is recommended that before you upload your lookup table into CommCare HQ that you eliminate the spacing between the words. Example: marie france > marie_france

How: (numbered steps correspond to the images below)

1. Select the column(s) where the data are located. Then click "Ctrl + F" to show the "Find" box. Go to the tab "Replace" and in the "Find what:" entry box, type a space.

2. In the "Replace with:" entry box, type an underscore "_"

3. Click "Replace all."

Image Added

Image Added

Image Added

How to quickly append text/numbers to the end a text:

=CONCATENATE

Use: Imagine that you have a list of districts and another list of villages. Some of the districts have the exact same name as the villages (Ex: District: Bambara, which contains a village also called Bambara). CommCare will require you to distinguish which of the "Bambara" is a village and which is a district. For this example, we will append "_vil" to the end of each village name. 

How: Create a column next to the one containing the words you want to append. Use =CONCATENATE(cell number, "what you want to append"). In this case, we appended _vil. 

When clicking on the new value in the column "Appended Village", you'll notice that the value of the cell is still the formula. Select the first cell and then click ctrl+shift+down arrow (this will select all values in this column) and click ctrl+c to copy the selection. Then right click and choose 'paste values' under paste options. This eliminates the formula so that you are only left with the values you want (ie, the text in the desired format).

Image Added

How to quickly eliminate duplicate entries from your Lookup list:

Use: To link villages to districts, you need one row per village. But because multiple villages can belong to a single district, this means as a result that you will have several rows where your district is repeated. CommCare will require a separate list for districts and a separate one for villages. For that reason, you will want to eliminate all the duplicate districts from your Excel spreadsheet.

How: (numbered steps correspond to the images below)

1.. Select the columns containing duplicate data. Click "Data" on the toolbar, then click "Remove Duplicates"

2.. Make sure that you select that your data has headers (if it does), and that you have selected the entire column. Click OK. 

3.. You will see a message box with the number of duplicate values that have been removed and the number remaining.

Image Added

Image Added

Image Added

If you want to be able to see which duplicate values were removed

1. In a new tab, copy - paste your original column with duplicates into column A

2. Copy - paste your new cleaned up column into column B

3. In column C, enter the following countif equation: "=COUNTIF(B1:B800, A1)"

4. In any row that has the value "2" in column C, the column A value is a duplicate

How to (very) quickly fill in a column with a value or a formula:

If you want to quickly pull down a single value, you can double click (don’t drag) on the bottom right corner and the value will fill in bellow for all rows where there is information next to it.  

The RED X shows you were to double click. 

Image Added

AND the same thing can be done with Functions!  But don't forget to copy and paste value once you've created a column of functions. 

Image Added

First, upload the following z-score table using the information in this section. This table covers children between 0 and 60 months of age, but you can modify the table as needed for your project. 

zscore.xlsx

Including Lookup Table Information in the Form

Once we've defined a zscore lookup table, we need to reference it in our form.  Edit the form XML and add an "instance" line that references the name of the table.  You can place this above any existing instance tags in your form.  

Calculating the Z-Score

In your form, first add three questions, one for the gender, age in months and measured weight.  (You can also load these from a case if you've previously captured them elsewhere).  Note-- your answer options for the gender question MUST be "female" and "male" (not "m", "f" or "1", "2") to align with what is written in the z score look up table. 

Image Removed

Then add a hidden value (zscore) to calculate the approximate z-score from the table.  Assuming the name of your lookup table is "zscore" the calculation for this hidden value should be:

Note: This calculation uses case properties named "gender", "weight", and "age." If you have named your case properties something else, you will need to change the reference above to the same name as your case properties. 

This hidden value will be -3, -2, -1, or 0 which corresponds to the child being more than -3, -2, and -1 standard deviations from the mean.  You can then use this hidden value for further calculations or display it back to the user. testing your app with lookup tables, try to clear user data. After you sync your phone if the lookup table still is not working, you may need to clear user data and log-in again. To do this, go to the login screen and click on Settings. After the Settings screen shows up, click the menu button, and select the first option, "Clear User Data." This will clear the username and force you to re-enter login credentials. Once you log in again, the new or edited table should be accessible to your user.

Training an application with lookup tables

1. Generic User: Create a generic user called "dimagi" or "demo" and update the table specifically for this user - populating all the conditional select options with the same 'dimagi' or 'demo'. As long as the partner is aware of the use case, they will be able to filter out this user in reports. During training, mobile users will sign onto CommCare and submit live 'practice' data. Work with the partner to specify a date where all data submitted to CommCare HQ is considered actual data for program purposes. This should be made clear to the FLWs and to the data analyst/M&E officer.

2. Training Application: Copy the final application - remove the conditional select options from the application. This app can be used during training as a training application and the users can enter data in demo mode.