topic Re: Error in Data load editor script. Could someone please help? in App Development

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

Sushmitha_tallapudi — Sat, 16 Nov 2024 04:57:58 GMT

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

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

treysmithdev — Wed, 14 Aug 2019 22:56:32 GMT

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

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

Sushmitha_tallapudi — Thu, 15 Aug 2019 19:01:26 GMT

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

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

treysmithdev — Thu, 15 Aug 2019 19:31:12 GMT

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