how to use excel files for list of available codes

[crc]

Dear CSPRO team,

I am a new user of this application and find this extremely useful. I tried my hands on creating a simple application but stumbled upon the following issue:

I have attached the application and the excel file required in creating in what I am trying to achieve.
The attached cspro application have two variables R_DIST and J_CODE. R_DIST is the districts for which I have already defined the value labels, but for J_CODE which is actually a "Janpad Code" meaning Sub-districts, I have the excel file where the mapping of the districts with their respective sub-districts are defined.

Please help me with the syntax/code/process through which I can have the required drop down list of sub-districts for a specific district.

I was not able to work with the examples in CSPRO folder for LOOK-UP files and was not able understand the exact process, it would help if you can provide the syntax and process details for the above issue.

[josh]

We did a very similar exercise in a recent workshop. In our case we had districts and villages in those districts with the mapping from villages to districts in an Excel file. As you have already figured you need to use a lookup file which can be a bit tricky the first time you do it.

You can find the lecture notes and an example at the following links:

http://teleyah.com/cspro/DCJune2015/05- ... -entry.pdf
http://teleyah.com/cspro/DCJune2015/05- ... y5-6.1.zip

The Excel spreadsheet containing villages and districts in the annex of the example questionnaire which can be found here:
http://teleyah.com/cspro/DCJune2015/Exa ... naire.xlsx

Take a look at this example and if you have additional questions please post them here.

[josh]

Someone pointed out that the example code above does not work in the current version of CSPro (thanks Noel). Please use the following link instead:

http://teleyah.com/cspro/DCJune2015/05- ... y5-6.1.zip

[Guest]

Thanks a lot Josh, now I am able to execute lookup files.

[Benjy]

Hi Josh,

I'm now giving a try to lookup files, to create value sets for districts (drop list) depending on the keyed province code (not much different from the example mentioned in this post). I'm struggling with the Excel2cspro tool: I should get 3 columns (province code, district code, district name) but the tool creates another column (in front of the other three) with value "1" for all cases. Not sure about what I'm doing wrong. Do you need to see the Excel file and the DAT file to understand the issue clearly?

Also, if I want to to the same for communes in districts, and villages in communes (4 administrative division levels), do I need to create a lookup file/dictionary at each time?

Thanks a lot,

Benjamin.

[josh]

The "1" in the first column is the record type. This is how CSPro tells which kind of record is on each line in the data file when you have more than one kind of record such as a housing record and a population record. Usually in a lookup file you only have one type of record so this isn't needed although it also isn't doing any harm. If you want to get rid of it you can modify the dictionary so that length of the record type is zero and then rerun Excel2CSPro using the modified dictionary.

It is possible to use a single data file and dictionary for the communes and villages but the logic to do it is rather complex so it may be simpler to just create multiple data files and dictionaries. I'm attaching an example of using a single data file but as I said, the logic is not easy to follow so you may just want to stick with multiple dictionaries.

[htuser]

Dear Josh,
I will have to implement the same logic soon for multiple level: Strata1,Enumerators name, Interviewer's name, Interviewer's address, Housing geographic coordinates of the interviewer . I would like to know if it's possible to follow the latest logic you posted by using a single file with 5 columns, then implement cascading questions?

About difficult/complexe logic, as you know, one of the main advantage of Cspro over others CAPI framework is its powerful programming language. This allow to implement very complexes task impossible to do with others.
Thanks in advance!

[josh]

@htuser - this should be possible with some small modifications to the example above. Note that the use of locate inside the loop to skip over records in the example is really an optimization to deal with a potentially large lookup file like a Census where you might have 30,000+ records. For a smaller file, as you would have typically with a sample survey, it would be perfectly fine to search through all the records in the file using a single call to locate at the start and then consecutive calls to loadcase. This logic would be much simpler and would probably be fast enough if the lookup file is of a reasonable size.

For example for the village value set you would add every case from the lookup file that matched the province chosen, had a non-blank code for district and had a blank code for village:

locate(GEO_LOOKUP_DICT,>=, "");
while loadcase(GEO_LOOKUP_DICT) <> 0 do
    if LU_PROVINCE = PROVINCE and LU_DISTRICT <> notappl and LU_VILLAGE = notappl then
        errmsg("%d %d %s", LU_PROVINCE, LU_DISTRICT, LU_NAME);
        // Add village to value set
        labels(nextEntry) = LU_NAME;
        codes(nextEntry) = LU_DISTRICT;
        nextEntry = nextEntry + 1;
    endif;
enddo;

Then for the village you would take everything that matched the province and the district and had a non-blank village:

numeric nextEntry = 0;
locate(GEO_LOOKUP_DICT,>=, "");
while loadcase(GEO_LOOKUP_DICT) <> 0 do
    if LU_PROVINCE = PROVINCE and LU_DISTRICT = DISTRICT and LU_VILLAGE <> notappl then
        // Add village to value set
        labels(nextEntry) = LU_NAME;
        codes(nextEntry) = LU_VILLAGE;
        nextEntry = nextEntry + 1;
    endif;
enddo;

I might test this solution first and if it seemed fast enough then use it and if not then try the technique with locate in the example as an optimization. Note that you will want to test it on the device the enumerators will use as it may be very fast on the PC but slower on a phone or tablet.

[htuser]

Thanks Josh. I'll test it and send results to Csprousers!
Htuser,

[Benjy]

I thought that my lookup procedure was not working because of this "record number" column but just noticed I forgot to declare labels and codes alpha variables in the PROC GLOBAL... Now everything works fine. I'll try the single lookup file (including all levels of coding) later as for now I'm just happy to get a functioning simple procedure!
Again, thanks a lot.