Script to transform data from SalesForce

Anonymous — Tue, 22 Nov 2016 18:08:28 GMT

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...

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

Anonymous — Tue, 22 Nov 2016 20:22:35 GMT

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;

topic Re: Script to transform data from SalesForce in App Development

Script to transform data from SalesForce

Re: Script to transform data from SalesForce