Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

brianrmacdonald
Contributor

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
brianrmacdonald
Contributor

Re: Script to transform data from SalesForce

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;