9 Replies Latest reply: Feb 8, 2018 8:42 PM by Rachel Zhong RSS

    Load table with condition

    Rachel Zhong

      Hi all, I now have a problem about loading table from existing table with condition.

      I have connected Survey Monkey and want to select some fields from the table. For example I would like to chose records which field 'question' includes key words like " postive impacts" or " business outcomes". But I can load the table with condition so I load the whole table first.

      The script I wrote for load the table is:

       

      [Mid_Program Participant template]:

      LOAD

         respondent_id as [GetResponses.respondent_id],

         answer as [GetResponses.answer],

           question as [GetResponses.question];

       

      SELECT respondent_id,

             question,

             answer

      FROM GetResponses

      WITH PROPERTIES (

        surveyId= '128081116'

      );

       

      Then I worte another script to load these fields to another table and added some new fields,

       

      [Mid_Program Participant]:
      When wildmmatch( quesion,'postive impacts') + wildmmatch( quesion,'business outcomes')

      LOAD

         [GetResponses.respondent_id],
         [GetResponses.question],
         [GetResponses.question],
          'CLientA' as Mid_ProClient,

           'Leadership' as Online_event,

          'LocationA' as Online_occurance
      Resident [Mid_Program Participant template1];

       

      Drop table [Mid_Program Participant template1];

       

      Unfortunatly these scripts did't work, any suggestion?

        • Re: Load table with condition
          Thiago Justen Teixeira

          Maybe this will help you:

           

          [Mid_Program Participant]

           

          LOAD

            [GetResponses.respondent_id],
            If (WildMatch ('[GetResponses.question]', '*positive impacts*,'*business outcomes*')>0,[GetResponses.question]) as question,

            [GetResponses.question],
              'CLientA' as Mid_ProClient,

              'Leadership' as Online_event,

              'LocationA' as Online_occurance
          Resident [Mid_Program Participant template1];

           

          Drop table [Mid_Program Participant template1];

            • Re: Load table with condition
              Rachel Zhong

              Hi Thiago, I have tried something below and it worked

               

              [Mid_Program Participant template]:

              LOAD

                 respondent_id as [GetResponses.respondent_id],

                 answer as [GetResponses.answer],

                   question as [GetResponses.question];

              SELECT respondent_id,

                     question,

                     answer

              FROM GetResponses

              WITH PROPERTIES (

                surveyId= '128081116'

              );

               

              [Mid_Program Participant]:

              LOAD

                 [GetResponses.respondent_id] as Mid_PP_ID,

                  'CLientA' as Mid_PClient,

                  'Leadership' as Mid_PEvent,

                  'LocationA' as Mid_POccurance,

                 [GetResponses.question] as Mid_PP_Question, 

                 [GetResponses.answer] as Mid_PP_Answer

              Resident [Mid_Program Participant template]

                 Where WildMatch([GetResponses.question],'*Changes at work*') or  WildMatch([GetResponses.question],'*outcome*');

               

              But now I would like to load some data to  [Mid_Program Participant] from another table with the same way, and I use :

              [Mid_Program Participant template]:

              LOAD

                 respondent_id as [GetResponses.respondent_id],

                 answer as [GetResponses.answer],

                   question as [GetResponses.question];

              SELECT respondent_id,

                     question,

                     answer

              FROM GetResponses

              WITH PROPERTIES (

                surveyId= '128084936'

              );

               

               

               

              LOAD

               

                 [GetResponses.respondent_id] as Mid_PP_ID,

                  'CLientB' as Mid_PClient,

                  'Teamwork' as Mid_PEvent,

                  'LocationB' as Mid_POccurance,

                  [GetResponses.question] as Mid_PP_Question, 

                 [GetResponses.answer] as Mid_PP_Answer

              Resident [Mid_Program Participant template]

              // filter the rows we need

                 Where WildMatch([GetResponses.question],'*Changes at work*') or  WildMatch([GetResponses.question],'*outcome*');

              Drop table [Mid_Program Participant template];

               

              There are some mistakes about this part, not sure which part is wrong. Any suggestions?

            • Re: Load table with condition
              Thiago Justen Teixeira

              Rachel,

               

              Any news about your question? Did you solve the problem?

               

              Cheers