Accessing Paradata fields

Discussions about tools to complement CSPro data processing
Forum rules
New release: CSPro 8.0
Post Reply
AriSilva
Posts: 591
Joined: July 22nd, 2016, 3:55 pm

Accessing Paradata fields

Post by AriSilva »

Hi folks,
Just as a background, I already tried several times to work with paradata, but I always throw the towel when it comes to the query part of it, mainly because my lack of experience with SQL.
But this time I really want to tackle this subject, and my question now is:
Where can I find information for all the fields that the cslog contains?
I see the examples in the PARADATA help sheet mentioning fields such as
operatorid_info
session_info
event_start.time
sesssion_event_end
etc
Where do they come from?
Do you have any other examples of reading a paradata file?
I took a look at the paradataViewer tool, and it seems very interesting, but besides that, I need to really get data for every interviewer/variable timing. For example, I want to have an array containing an entry for each operator id, case_id, variable_id, time_Start and time_end.
So, by using this array, I would be able to have averages of the time consumed by each field and/or each operator.
For example, operators taking too fie times for such a variable might mean that s/he is not even reading the question to the interviewer, since the registered time in the paradata is shorter than the time to read the question.
My other interest in the paradata is related to the gps function.
Now we are dealing with those issues by hand, inside the cspro program, collecting the gps and the time for each question, which is not the best solution.
That is, we really need to move forward into using the paradata as it should be. I´m not sure that other users like us "abandoned" the paradata, or indeed how many users are dealing with the subject.
Best
Ari
Best
Ari
Gregory Martin
Posts: 1777
Joined: December 5th, 2011, 11:27 pm
Location: Washington, DC

Re: Accessing Paradata fields

Post by Gregory Martin »

The best way to learn about paradata is to peruse the paradata tables. In the Paradata Viewer, select View -> Table Browser: https://www.csprousers.org/help/Paradat ... owser.html

From there, you'll see a list of all the tables in the file. Everything emanates from an event, so you can look at the base event table and then see what specific event table it's linked to. You may have to look at the _info or _instance tables to get the particulars about a specific event. Those tables exist so that we don't repeat information over and over in an event table.

Another way to learn is to study the report queries. For example, the first report ("Field Entry Information") shows each question and how long it took to answer it. Select Options -> Show SQL Statements Pane and you can see the SQLite query used to generate the table. The queries can be pretty daunting, but with practice you may get the hang of it.

If you have a specific query you want help writing, let us know. It's always a fun challenge to come up with queries.
AriSilva
Posts: 591
Joined: July 22nd, 2016, 3:55 pm

Re: Accessing Paradata fields

Post by AriSilva »

I´ve written a post reply a week ago, and it seems to have "disappeared".
I was asking if there was any white paper describing the tables in the paradata files, and I´ve sent a small paradata example for Gregg to have fun with that.
Best
Ari
Best
Ari
AriSilva
Posts: 591
Joined: July 22nd, 2016, 3:55 pm

Re: Accessing Paradata fields

Post by AriSilva »

Is there a "map" connecting the table´ids?
How can I join the field_entry_event with the case_key_info?
Plz,
I need a query that outputs the case_id, the field_name and the duration, that is:
SELECT case_key_info.key,
name.name,
field_entry_event.display_duration
Thanks
Ari
Best
Ari
savy
Posts: 159
Joined: December 27th, 2012, 1:36 pm

Re: Accessing Paradata fields

Post by savy »

Ari,

Here is the query that should work for you. Paradata ->Options->"Show SQL Statements" will give you insights on the tables involved.

Code: Select all

SELECT case_instance.id,
       event.id                           AS fieldEventId,
       case_key_info.KEY,
       NAME.NAME                          AS fieldName,
       field_entry_event.display_duration AS duration
FROM   field_entry_event
       JOIN event
         ON field_entry_event.id = event.id
       JOIN field_entry_instance
         ON field_entry_event.field_entry_instance = field_entry_instance.id
       JOIN field_info
         ON field_entry_instance.field_info = field_info.id
       JOIN NAME
         ON field_info.field_name = NAME.id
       JOIN case_instance
         ON event.case_instance = case_instance.id
       JOIN case_key_info
         ON case_instance.case_info = case_key_info.case_info
ORDER  BY case_instance.id; 
AriSilva
Posts: 591
Joined: July 22nd, 2016, 3:55 pm

Re: Accessing Paradata fields

Post by AriSilva »

Great!
That´s exactly what I needed.
I already executed and it works.
Thank you very much, savy
From this example, I think I can manage to walk through the paradata tables, although, as a future new request, I would like to have some documentation on the paradata tables, and how they relate to each other.
Best
Ari
Best
Ari
Post Reply