Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Script to transform data from SalesForce

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.

1 Reply
Anonymous
Not applicable
Author

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;