Group Data in Script

[hammermill21]

Hello everyone,

I have data being pulled in that is shown as q1, q2, q3, q4 and so on. What I need to do is group the question. SO q1 - q5 would be called Pre-Drill and q6-q8 is Rescue and so on like that.

This is what my scrip currently looks like:

[record]:

LOAD [ID] AS [ID],

[CREATED_DATE] AS [CREATED_DATE],

[CREATED_BY] AS [CREATED_BY],

[CREATED_LOCATION] AS [CREATED_LOCATION],

[building] AS [building],

[department] AS [department],

[quarter] AS [quarter],

[shift] AS [shift],

[drill_type] AS [drill_type],

[announced_drill] AS [announced_drill],

[insp_date_time] AS [insp_date_time],

[conductor] AS [conductor],

[q1] AS [q1],

[q2] AS [q2],

[q3] AS [q3],

[caption] AS [caption],

[email] AS [email],

[attendance] AS [attendance],

[additional_pictures] AS [additional_pictures],

[email_record] AS [email_record],

[open_date__time_of_form] AS [open_date__time_of_form],

[q4] AS [q4],

[q5] AS [q5],

[q6] AS [q6],

[q7] AS [q7],

[predrill_evaluation_total_percent] AS [predrill_evaluation_total_percent],

[q8] AS [q8],

[contain_total_percent] AS [contain_total_percent],

[rescue_total_percent] AS [rescue_total_percent],

[activate_total_percent] AS [activate_total_percent],

[q9] AS [q9],

[q10] AS [q10],

[q11] AS [q11],

[q12] AS [q12],

[q13] AS [q13],

[q14] AS [q14],

[extinguish_total_percent] AS [extinguish_total_percent],

[q16] AS [q16],

[q15] AS [q15],

[q17] AS [q17],

[q18] AS [q18],

[evacuate_the_area_total_percent] AS [evacuate_the_area_total_percent],

[q19] AS [q19],

[q20] AS [q20],

[q21] AS [q21],

[q22] AS [q22],

[hospital_fire_place_race_total_percent] AS [hospital_fire_place_race_total_percent],

[q23] AS [q23],

[q24] AS [q24],

[q25] AS [q25],

[q26] AS [q26],

[q27] AS [q27],

[q28] AS [q28],

[q29] AS [q29],

[q30] AS [q30],

[personnel__total_percent] AS [personnel__total_percent],

[building__total_percent] AS [building__total_percent],

[average_score] AS [average_score],

[floor] AS [floor],

[device_id] AS [device_id],

[offsite_fire_responders_notification__90_secs] AS [offsite_fire_responders_notification__90_secs],

[type_of_signal] AS [type_of_signal],

[operator_name] AS [operator_name],

[operator_id] AS [operator_id],

[q11a] AS [q11a],

[drill_notes] AS [drill_notes],

[audio] AS [audio],

[device_type] AS [device_type],

[scenario] AS [scenario],

[timer_test] AS [timer_test],

[other_scenario] AS [other_scenario],

[q40] AS [q40],

[__FK_record] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_record]);

[coordinate]:

LOAD [@Value] AS [@Value],

[__FK_coordinate] AS [__KEY_location]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_coordinate]);

[location]:

LOAD [type] AS [type],

[__KEY_location] AS [__KEY_location],

[__FK_location] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_location]);

[root]:

LOAD [__KEY_root] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__KEY_root]);

DROP TABLE RestConnectorMasterTable;

[vamsee]

Hello,

Your script is from a resident table.

If you are loading them using different table(qvd's), then create a new column 'Pre Drill' as Group during that load.

If that's not case and the load is happening from only one source then can you please explain what would be the Values in q1-q5 when its rescue records.

Would they be blanks?

[hammermill21]

Yes they are all loading from the same table. What I'm trying to do is group the questions because I have to score them based on their answer.

[vamsee]

I hope I got you right.

For this, you should be doing multiple residents and concatenate them. In this case 30.

This might not be suggestable if your Questions increase going forward.

Example:

Group_Table:

LOAD

          *,

          [q1] AS Pre_Drill

Resident record;

Concatenate(Group_Table)

LOAD

          *,

          [q2] AS Pre_Drill

Resident record;

.

.

.

.

Concatenate(Group_Table)

LOAD

          *,

          [q6] AS Rescue

Resident record;

and so on.