3 Replies Latest reply: Aug 16, 2018 2:56 PM by vamsee duggirala RSS

    Group Data in Script

    Elizabeth Viso

      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;

        • Re: Group Data in Script
          vamsee duggirala

          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?

            • Re: Group Data in Script
              Elizabeth Viso

              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.

                • Re: Group Data in Script
                  vamsee duggirala

                  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.