1 Reply Latest reply: Nov 22, 2016 3:22 PM by Brian MacDonald RSS

    Script to transform data from SalesForce

    Brian MacDonald

      I have some data that is being retrieved from SF using the Qlik SF connector.  When I do the initial load the data set I get looks like this...

      SF Output.jpg

      So what I want to do is to replace nulls the Lead.Id field so I can do measures (counts, etc.) on the Subject field by lead

       

      Here is the script I am trying to get working:

       

      activity_data_raw:

      // this generates the data in the screen shot above

      SELECT

        Id,

          (SELECT Subject, ActivityType FROM ActivityHistories)

        FROM Lead LIMIT 5 ;

         

       

      activity_data:

      // here I am trying to loop through the rows and replace null values of Lead.Id with the last non-null value of Lead.Id

      Let numRows = NoOfRows('activity_data_raw');

      Let myId = 'Initialize';

      For myRow = 1 to numRows

           // for debugging

         Let currentId = FieldValue('Lead.Id', myRow);

         Let currentSubject = FieldValue('Lead.ActivityHistories.Subject', myRow);

       

        // set the value of myID

        If FieldValue('Lead.Id', myRow) <> null() then

          Let myId = FieldValue('Lead.Id', myRow);

        End If

         

           // replace null values of LeadId with the most recent non-null value

          If FieldValue('Lead.Id', myRow) = null() Then

               Load myID AS LeadId

              , Lead.ActivityHistories.Subject AS Subject

                  , Lead.ActivityHistories.ActivityType AS ActivityType

                  Resident activity_data_raw

                  ;

          End If

      Next myRow

       

      What happens here is that on the first pass the variable myID gets the value of Lead.Id in the screenshot.  On the second pass it gets a new value, the rows where Lead.Id is null are skipped.  Also, on the first pass, the value of Subject is not null as is it in the screenshot.

       

      So how should I go about this.  How do I do row by row processing of a loaded table?

       

      Thanks in advance.

        • Re: Script to transform data from SalesForce
          Brian MacDonald

          I think I figured this out.  Here is the modified script.  The trick was using the Peek function.  I also had some issues with the Load statement in the previous script which never executed previously.  I solved that with an Inline Load Statement.

           

          activity_data_raw:

          Load

            RowNo() AS rownum

            , Lead.Id

              , Lead.ActivityHistories.Subject

              , Lead.ActivityHistories.ActivityType

              ;

             

          SELECT

            Id,

              (SELECT Subject, ActivityType FROM ActivityHistories)

            FROM Lead LIMIT 5

              ;

             

          activity_data:

          Let numRows = NoOfRows('activity_data_raw');

          Let myId = 'Initialize';

          For myRow = 0 to numRows

            Let currentId = Peek('Lead.Id', myRow, 'activity_data_raw');

              Let currentSubject = PurgeChar(Peek('Lead.ActivityHistories.Subject', myRow, 'activity_data_raw'),'[],');

              Let thisType = SubField(currentSubject, ':',1);

              Let thisDesc = SubField(currentSubject,':',2);

             

            If currentId <> null() then

              Let myId = Peek('Lead.Id', myRow, 'activity_data_raw');

              Else     

              Load * Inline

                  [

                      leadId, activityType, activityDescription

                  $(myId), $(thisType), $(thisDesc), $(currentActivityType)

                      ];

              End If

          Next myRow

           

           

          Drop Table activity_data_raw;