Error handling in load script

[Nithin29]

Hi,

Please can anyone help me with the error handling in scripts.

I have 5 data load scripts. If one script is encountered with error i want to skip th error and execute the remaining 4 scripts.I am using gcp bigquery as a datasource. Below is the logic i tried. it is working partially. For example, if have an error in 4th script till 3rd data is loading and stopped loading at the 4th script. even though there is no error in 5th script it is also considered as error. I want to skip the 4th and jump on to execute the 5th script

SET ErrorMode = 0; // Allow script to continue on error

[Table]:
SELECT
*
FROM `XXXX`;

IF ScriptErrorCount > 0 THEN
TRACE ⚠️ Table load failed. Skipping...;
// DROP TABLE [Table];
ENDIF

[Vegar]

The ScriptErrorCount will continue to accumulate with +1 for each error in the script. Try using ScriptError instead, that will reset it self to 0 after every successful script statement.

SET ErrorMode = 0; // Allow script to continue on error

[Table]:
SELECT *
FROM `XXXX`;

IF ScriptError > 0 THEN
    TRACE :warning: Table load failed. Skipping...;
    // DROP TABLE [Table];
ENDIF

[Nithin29]

Thank you Vegar but it still behaves like the same above. 

[Lech_Miszkiewicz]

Hi.

I tend to reset ScriptErrorCount variable within each IF -- ENDIF statement like below:

IF ScriptErrorCount > 0 THEN
    TRACE ‌‌ Table load failed. Skipping...;
    TRACE: resetting script error count;
    SET ScriptErrorCount = 0 ;
// DROP TABLE [Table];
ENDIF

[Lech_Miszkiewicz]

ohhh.. one more thing... at the beginning I also configure this variable and set it by default to 0 to ensure that it is not null()

SET ScriptErrorCount = 0 ;

[marksouzacosta]

Hi @Nithin29 ,

I'm not sure if I understand your logic. You have 5 Scripts that do something - like your example, SELECT * FROM Table; Let's call these Script1,Script2..Script5.

If Script1 fails:
Call Script2 and ignore errors.
Call Script3 and ignore errors.
Call Script4 and ignore errors.
Call Script5 and ignore errors.

What happens if Script1 succeed?

Or would that be like a chain of tries?
If Script1 fails, call Script2.
If Script2 fails, call Script3.
If Script3 fails, call Script4.
If Script4 fails, call Script5.

Can you please clarify?

Regards,

Mark Costa

[Nithin29]

Thank you. tried but not working sorry

[Nithin29]

Hi Mark,

I have 5 scripts which loads data from 5 different tables. 

If script 1 loading is succeeded then it move on to script2,3,4,5.

similarly, if script1 is failed due to error(for ex: invalid dimension) then the entire load will be stopped. But I wanted to skip the error or data load for script1 and move on to execute the script 2,3,4,5.

Set ErrorMode = 0; // Prevent script from stopping on error

// Script 1
Trace Loading Script 1;
Script1:
LOAD * FROM [lib://YourDataSource1]
...;
IF ScriptErrorCount = 0 THEN
    Trace Script 1 loaded successfully;
ELSE
    Trace Script 1 failed, skipping;
ENDIF

// Script 2
Trace Loading Script 2;
Script2:
LOAD * FROM [lib://YourDataSource2]
...;
IF ScriptErrorCount = 0 THEN
    Trace Script 2 loaded successfully;
ELSE
    Trace Script 2 failed, skipping;
ENDIF

// Script 3
Trace Loading Script 3;
Script3:
LOAD * FROM [lib://YourDataSource3]
...;
IF ScriptErrorCount = 0 THEN
    Trace Script 3 loaded successfully;
ELSE
    Trace Script 3 failed, skipping;
ENDIF

// Script 4
Trace Loading Script 4;
Script4:
LOAD * FROM [lib://YourDataSource4]
...;
IF ScriptErrorCount = 0 THEN
    Trace Script 4 loaded successfully;
ELSE
    Trace Script 4 failed, skipping;
ENDIF

// Script 5
Trace Loading Script 5;
Script5:
LOAD * FROM [lib://YourDataSource5]
...;
IF ScriptErrorCount = 0 THEN
    Trace Script 5 loaded successfully;
ELSE
    Trace Script 5 failed, skipping;
ENDIF

Set ErrorMode = 1; // Restore default error behavior

this is the logic i tried

[marcus_sommer]

I think the ScriptErrorCount isn't really suitable to control the task-flow. Better would be to take ScriptError like Vegar suggested and also to trace the kind of error:

ScriptError | Qlik Cloud Help

[marksouzacosta]

Got it.

Try the code below, right after each load statement:

IF $(#ScriptError) > 0 Then
	Trace Script 1 failed, skipping;
Else
	Trace Script 1 loaded successfully;
End If

Regards,

Mark Costa