Error in Data load editor script. Could someone please help?

[Sushmitha_tallapudi]

I'm trying to run few lines of script in data load editor.  But, it is throwing an error.  Could anyone please help on where I'm going wrong?

tmp2:

LOAD
[Brand Name]
,[Month]
,[Name]
,[First Name]
,[Email ID]
,Avg([Forecast1]) as [Last Month Forecast]
,Avg([Forecast2]) as [This Month Forecast]
,Avg([% Forecast Difference])
,Avg([Target %Diff])
Resident tmp1

Group By [Brand Name], [Month],[Email ID],[Name],[First Name];

SUB SetCursor (SetCursor.table, SetCursor.record)
LET SetCursor.n=1;
FOR SetCursor.n = 1 TO NoOfFields(SetCursor.table)
LET SetCursor.col = FieldName(SetCursor.n, SetCursor.table);
LET [$(SetCursor.col)] = Peek(SetCursor.col, SetCursor.record - 1, SetCursor.table);
NEXT

LET SetCursor.n = Null();
LET SetCursor.table = Null();
LET SetCursor.col = Null();
END SUB

For n=1 To NoOfRows('tmp2')
CALL SetCursor('tmp2',n);
If $([% Forecast Difference]) < $([Target %Diff]) then
LET vMSg = 'Dear $([First Name])'
& '<br><br> This message is sent from Qlik Sense.';
/* & '<br> This month your demand forecast for the month of $([Month]) is more than 10% of that has been last month'
& '<br> Last month forecast for $([Month]) is $([Last Month Forecast]) and the current month forecast for $([Month]) is $([This Month Forecast]';

CALL SendMail(1, [Email ID],'', 'Huge Demand Forecast Variation',vMsg);
*/
endif
NEXT

[treysmithdev]

Not sure about the other parts of your script, but the below should work.

Also, it would probably make your life easier if you removed the spaces from your field names. Makes things a lot harder to debug. 

tmp2:

LOAD
[Brand Name]
,[Month]
,[Name]
,[First Name]
,[Email ID]
,Avg([Forecast1]) as [Last Month Forecast]
,Avg([Forecast2]) as [This Month Forecast]
,Avg([% Forecast Difference])
,Avg([Target %Diff])
Resident tmp1

Group By [Brand Name], [Month],[Email ID],[Name],[First Name];

SUB SetCursor (SetCursor.table, SetCursor.record)
 //    LET SetCursor.n=1; // don't need this

    FOR SetCursor.n = 1 TO NoOfFields('$(SetCursor.table)')  // dollar sign expansion + quotes 
        LET SetCursor.col = FieldName($(SetCursor.n), '$(SetCursor.table)'; // dollar sign expansion + quotes
        LET [$(SetCursor.col)] = Peek('$(SetCursor.col)', $(SetCursor.record) - 1, '$(SetCursor.table)'); // dollar sign expansion + quotes where needed
    NEXT

    LET SetCursor.n = Null();
    LET SetCursor.table = Null();
    LET SetCursor.col = Null();
END SUB

 

For n=1 To NoOfRows('tmp2')
    CALL SetCursor('tmp2',n);
    If $(% Forecast Difference) < $(Target %Diff) then // Had to remove variable brackets
        LET vMSg = 'Dear $([First Name])'
        & '

 This message is sent from Qlik Sense.';
        /* & '
 This month your demand forecast for the month of $([Month]) is more than 10% of that has been last month'
        & '
 Last month forecast for $([Month]) is $([Last Month Forecast]) and the current month forecast for $([Month]) is $([This Month Forecast]';

        CALL SendMail(1, [Email ID],'', 'Huge Demand Forecast Variation',vMsg);
        */
    endif
NEXT

[Sushmitha_tallapudi]

HI, I;m still facing this issue. Here is the script modified as suggested by you and the error I'm getting:

ERROR:

Started loading data

The following error occurred:

The control statement is not correctly matched with its corresponding start statement

The error occurred here:

NEXT SetCursor.n

Data has not been loaded. Please correct the error and try loading again.

SCRIPT:

Unqualify *;

tmp1:

LOAD
[Brand Name]as [Brand_Name] ,
SUM([Forecast1]) as Past_Forecast,
SUM([Forecast2]) as Curr_Forecast,
((SUM([Forecast2])-SUM([Forecast1]))/SUM([Forecast1]))*100 AS [Variation]
FROM [lib://Documents/Data for Prescriptive analysis - No Links.xlsx]
(ooxml, embedded labels, table is Forecast1)

GROUP BY [Brand Name],[Month];

left Join

LOAD
[Brand Name] as [Brand_Name],
[Name],
[First Name] as [First_Name],
[Email ID] as [Email_ID],
[Target %Diff] as [Target_Diff]
FROM [lib://Documents/Data for Prescriptive analysis - No Links.xlsx]
(ooxml, embedded labels, table is Target);

tmp2:

LOAD
[Brand_Name]
,[Name]
,[First_Name]
,[Email_ID]
,Avg([Past_Forecast]) as [Past_Forecast]
,Avg([Curr_Forecast]) as [Curr_Forecast]
,Avg([Variation]) as [Variation]
,Avg([Target_Diff]) as [Target_Diff]
Resident tmp1

Group By [Brand_Name],[Email_ID],[Name],[First_Name];

For n=1 To NoOfRows('tmp2')
CALL SetCursor('tmp2',n);
If $(Variation) < $(Target_Diff) then // Had to remove variable brackets
LET vMSg = 'Dear $([First Name])'
& '

This message is sent from Qlik Sense.';
/* & '
This month your demand forecast for the month of $([Month]) is more than 10% of that has been last month'
& '
Last month forecast for $([Month]) is $([Last Month Forecast]) and the current month forecast for $([Month]) is $([This Month Forecast]';

CALL SendMail(1, [Email ID],'', 'Huge Demand Forecast Variation',vMsg);
*/
endif
NEXT

SUB SetCursor (SetCursor.table, SetCursor.record)

FOR SetCursor.n = 1 TO NoOfFields('$(SetCursor.table)') // dollar sign expansion + quotes
LET SetCursor.col = FieldName($(SetCursor.n), '$(SetCursor.table)'); // dollar sign expansion + quotes
LET [$(SetCursor.col)] = Peek('$(SetCursor.col)', $(SetCursor.record) - 1, '$(SetCursor.table)'); // dollar sign expansion + quotes where needed
NEXT SetCursor.n

LET SetCursor.n = Null();
LET SetCursor.table = Null();
LET SetCursor.col = Null();
END SUB

[treysmithdev]

Try this and see if anything worthwhile comes up through the log:

SUB SetCursor (SetCursor.table, SetCursor.record)

    Trace --------------------------- SetCursor: Table: $(SetCursor.table) - Record: $(SetCursor.record) START;
    FOR SetCursor.n = 1 TO NoOfFields('$(SetCursor.table)') // dollar sign expansion + quotes

        Trace ---------------------------- SetCursor: $(SetCursor.table)[$(SetCursor.record)] - Iter($(SetCursor.n)); 

        LET SetCursor.col = FieldName($(SetCursor.n), '$(SetCursor.table)'); // dollar sign expansion + quotes
        LET [$(SetCursor.col)] = Peek('$(SetCursor.col)', $(SetCursor.record) - 1, '$(SetCursor.table)'); // dollar sign expansion + quotes where needed
        
        LET vTmp = Peek('$(SetCursor.col)', $(SetCursor.record) - 1, '$(SetCursor.table)');
        Trace ---------------------------- SetCursor: $(SetCursor.table)[$(SetCursor.record)]: $(SetCursor.col): $(vTmp);
    
    NEXT SetCursor.n

    Trace --------------------------- SetCursor: Table: $(SetCursor.table) - Record: $(SetCursor.record) END;

    LET vTmp = Null();
    LET SetCursor.n = Null();
    LET SetCursor.table = Null();
    LET SetCursor.col = Null();

END SUB

Unqualify *;
tmp1:

LOAD
[Brand Name]as [Brand_Name] ,
SUM([Forecast1]) as Past_Forecast,
SUM([Forecast2]) as Curr_Forecast,
((SUM([Forecast2])-SUM([Forecast1]))/SUM([Forecast1]))*100 AS [Variation]
FROM [lib://Documents/Data for Prescriptive analysis - No Links.xlsx]
(ooxml, embedded labels, table is Forecast1)

GROUP BY [Brand Name],[Month];


left Join


LOAD
[Brand Name] as [Brand_Name],
[Name],
[First Name] as [First_Name],
[Email ID] as [Email_ID],
[Target %Diff] as [Target_Diff]
FROM [lib://Documents/Data for Prescriptive analysis - No Links.xlsx]
(ooxml, embedded labels, table is Target);


tmp2:

LOAD
[Brand_Name]
,[Name]
,[First_Name]
,[Email_ID]
,Avg([Past_Forecast]) as [Past_Forecast]
,Avg([Curr_Forecast]) as [Curr_Forecast]
,Avg([Variation]) as [Variation]
,Avg([Target_Diff]) as [Target_Diff]
Resident tmp1

Group By [Brand_Name],[Email_ID],[Name],[First_Name];

For n = 1 To NoOfRows('tmp2')

    Trace -------------------------------------------------------- Record: $(n);

    CALL SetCursor('tmp2',n);

    Trace -------------------------------------------------------- Record $(n): Condition(If $(Variation) < $(Target_Diff) then) ;

    If $(Variation) < $(Target_Diff) then // Had to remove variable brackets

        Trace -------------------------------------------------------- Record $(n): Message Sent;
        LET vMSg = 'Dear $([First Name])' & chr(10) & chr(10) &'This message is sent from Qlik Sense.';

    Else

        Trace -------------------------------------------------------- Record $(n): Skip;

    endif

NEXT n