Survey imports and attribute filtering

[Oneto12]

Hi,

I'm using QualCoder to evaluate about 1000 responses to a civic participation process in my community.
The data I want to import is coming from an .xlsx file with 3 columns: 'Name' / 'District' / 'Notes'
When I import the file via the 'import survey' option, I set 'Name' and 'District' as quantitative and 'Notes' as qualitative. QualCoder imports every 'Name' as a single case with 'District' as an attribute and the 'Notes' as a single file.

After the coding process, I want to filter my codes by the attribute 'District'. For example: What did people living in district 'xy' say about the street quality?

As the 'Notes' are just a single file, the filter by 'District' doesn't work and I'm always getting all codes from all 'Districts'.

Am I doing something wrong? Are there any better options for importing? What could be a work-around?

Thank you for any advice. Kind regards, Tim

[ccbogel]

Hi Tim. thanks for using QualCoder. Yes you have hit a limitation of the software. I had not envisiged this.
I am going away for 5 days, from today, but after this time I will work on a solution.
I am thinking I could write a SQL query that would link the case with the case attribute and the text positions within the notes file.

[Oneto12]

Hi, I have to say: Thank you(!) for developing Qualcoder and keeping it alive :-)

I am not that much into SQL but your idea sounds applicable. Anyways, I have the feeling that it would be even nicer to have QualCoder generate a file for every survey participant (case). I think this would make managing the codes much easier, than having them all in one file.

Thank you very much for helping out. Kind regards, Tim

[ccbogel]

I might make an option to import the qualitative component into separate files.
OK here is the solution:
Go to Reports > Database queries
In the top text section copy and paste this:

SELECT case_text.caseid, attribute.name as 'Case attribute', attribute.value, case_text.pos0, case_text.pos1,code_name.name,  code_text.pos0, code_text.pos1, seltext 
from 
case_text join attribute on attribute.id=case_text.caseid join source on source.id = case_text.fid 
join code_text on  code_text.fid=source.id join code_name on code_name.cid = code_text.cid 
where
 code_name.name = 'code1' 
and attribute.name='District' 
and attribute.value='north' 
and case_text.pos0 <= code_text.pos0 
and case_text.pos1 >= code_text.pos1 

Change the code name to the code you are interested in.
Alternatively , you can list a few codes by replacing the line with:
code_name.name in ('code1', 'code2')
Another alternativel, if you want all codes, remove that line and the next 'and' word.

Change north to another name that is in your District,
Alternatively replace that line with:
and attribute.value in ('north', 'south')

You can remove the pos0 and pos1 wording from the SELECT section. I kept them to confirm the SQL query was working correctly.

In the bottom section press the Run button.

with regards Colin

[Oneto12]

This is great! Thank you very much Colin. I definetly have to further improve my SQL knowledge.

Kind regards, Tim

[Oneto12]

Hi Colin,

I messed around with the SQL query for quite a while now and I think that I have a brief overview of the SQlite Data Structure. Anyways I found that there are some things not very clear to me...It seems that in some cases the code_text.pos and the case_text.pos don't fit well together.

In my understanding case_text.pos0 and case_text.pos1 comprise the whole text of one case. How is it possible then, that code_text.pos shows higher numbers than case_text? For more clarity I have attached a .csv file.

The code frequency I looked at has 34 entries, while I use the SQL query above I only get 32. The one's in the .csv are missing.

Could you please help me out? Or is there an (easy) way to transform the project (with coded text) to the newest version of qualcoder? I saw that you improved the option for survey import following to our discussion here. Thank you very much :-)

sql_report.csv

[ccbogel]

HI, for using the newest version of qualcoder, you need to download the code using the Green Code button and following the instructions on the main page to install and run it. Not as easy as using an exe, for now.

I looked as the csv file. I need to see the sql (is it very much the same sql as shown above?), and ideally be nice to see the original project, or a portion of it.
But when importing a survey, all the entire text of a qualitative column is turned into one long text file.
I presume for fid 61, that the text of the file is at least 2906 characters long.
The first case within that text is from positions 0 - 404.
So the sql command is not limiting the code text to match the case.

[Oneto12]

Hi Colin,

I finished fixing my project now and I can now export all the cases with their 'Districts' and their coded texts, as I wished. I will continue working with the data in R and will put my work with QualCoder on hold. Thank you very much for the nice software and all the support :-)

Following I will explain the problems I found with the code_text.pos and case_text.pos.

My project consists out of 448 files with shorter text files and 1 file with the long text file (containing the survey import).
For the problem described above, I figured out two reasons:

1. Single files: Some problems with shorter text files were results of my "handling" with the files. I initially created the files and coded them. Later on I added some text to the files and coded it as well. That's were the problem happened. The case_text.pos1 value in the database didn't get updated to the addition of the text. So I got code_text.pos positions which didn't fit case_text.pos positions. I fixed this by UPDATING the case_text.pos1 by the LENGTH(fulltext) from source.

2. Survey file: First of all - my source file is a bit long. It has a LENGTH(fulltext) of 214030. The coding process of the file was quiet cumbersome as the computer took very long for creating the database entries in code_text, the further I moved along in the file. At some points it took the PC about 20 seconds to put one code and QualCoder even said 'Not Responding'. This is, as I think, because QualCoder has to compute the code_text positions in a "high range value". I think this is another indication to have QualCoder generate a single file for every survey participant (case) at the import stage.

I had two approaches to fix this issue. The first one was to search for the coded text passage in the long text file, umark the existing code and code it again. This sometimes resolved the issue - as maybe the code_text.pos1 got calculated correctly, but I don't know. The second approach was to UPDATE the case_text.pos1 by the corresponding code_text.pos1 - which was mostly about 1 or 2 values (for example 206427 to 206429). It is lucky that in the long text file the cases have some "space" between their pos0 and pos1.

These are my experiences from the last days. Thank you very much for reading. I hope this maybe helps to improve the software even further. In case there are any questions - I'm happy to answer.

Best regards, Tim

[ccbogel]

@Oneto12 Thank you for the feedback.
Point 2: OK. I have in latest code updated the survey import, so that each qualitative column data produces one file per case.