topic Re: General Script Error in QlikView

General Script Error

shayraber — Sun, 18 Sep 2016 21:01:18 GMT

hi,

have a document which reloads 3 times a day.

most of the time with no errors at all.

sometimes, recently becomes more often, the script fails. at the same exact point (!!!)

no other document runs in parallel, so it's not an QVD issue.

the point which fails is at grouping by of a 33 million records table.

I see nothing special at the RAM usage.

what could be the reason?

thanks,

Shay

Re: General Script Error

Mon, 19 Sep 2016 01:18:09 GMT

Shay,

Would you be able to post the Script and mark the line where the error is occurring?

A screenshot of the error would be helpful as well,

Thanks!

- Stan

Re: General Script Error

Anil_Babu_Samineni — Mon, 19 Sep 2016 02:38:51 GMT

Please share the log file

Re: General Script Error

jagan — Mon, 19 Sep 2016 03:36:46 GMT

Hi,

Check whether the QVD file locked?

Regards,

Jagan.

Re: General Script Error

marcus_sommer — Mon, 19 Sep 2016 06:24:39 GMT

Your decriptions sounds that it could be resource-issue caused by a lack on CPU or RAM then a group by with 33 M records will need a lot of RAM - how do you check he RAM consumption?

I think you should consider to split respectively slice down these big aggregation-load and/or to implement an incremental load-approach. Here you will find many examples of them: Advanced topics for creating a qlik datamodel.

- Marcus

Re: General Script Error

shayraber — Mon, 19 Sep 2016 07:26:56 GMT

QVD is not locked due to the fact that nothing is running in parallel...

the document reloads one after another so there is no way the QVD is locked.

Re: General Script Error

shayraber — Mon, 19 Sep 2016 07:36:01 GMT

log file for failure, log file for success and the script can be found below:

thanks,

When Script Failed:

18/09/2016 23:12:10: 4253 FillRate19:

18/09/2016 23:12:10: 4254 LOAD

18/09/2016 23:12:10: 4255 Fr.Company

18/09/2016 23:12:10: 4256 ,Fr.Wharehouse

18/09/2016 23:12:10: 4257 ,Fr.Order#

18/09/2016 23:12:10: 4258 ,Fr.OrderLine AS Fr.OrderLine

18/09/2016 23:12:10: 4259 ,Fr.OrderIndex

18/09/2016 23:12:10: 4260 ,Fr.Item#

18/09/2016 23:12:10: 4261 ,Fr.StockTransType

18/09/2016 23:12:10: 4262 ,Fr.TransIdentity

18/09/2016 23:12:10: 4263 ,Fr.TransDate

18/09/2016 23:12:10: 4264 ,Fr.MaxEntryDate

18/09/2016 23:12:10: 4265 ,MaxInvDate

18/09/2016 23:12:10: 4266 ,Fr.Source

18/09/2016 23:12:10: 4267 ,Sum(Fr.TransQty) AS Fr.SumTransQty

18/09/2016 23:12:10: 4268 FROM ..\..\Model-Sales\QVD\Tables_Modified\FillRate20.qvd (qvd)

18/09/2016 23:12:10: 4269 GROUP BY

18/09/2016 23:12:10: 4270 Fr.Company

18/09/2016 23:12:10: 4271 ,Fr.Wharehouse

18/09/2016 23:12:10: 4272 ,Fr.Order#

18/09/2016 23:12:10: 4273 ,Fr.OrderLine

18/09/2016 23:12:10: 4274 ,Fr.OrderIndex

18/09/2016 23:12:10: 4275 ,Fr.Item#

18/09/2016 23:12:10: 4276 ,Fr.StockTransType

18/09/2016 23:12:10: 4277 ,Fr.TransIdentity

18/09/2016 23:12:10: 4278 ,Fr.TransDate

18/09/2016 23:12:10: 4279 ,MaxInvDate

18/09/2016 23:12:10: 4280 ,Fr.MaxEntryDate

18/09/2016 23:12:10: 4281 ,Fr.Source

18/09/2016 23:12:10: 4282

18/09/2016 23:19:48: General Script Error

18/09/2016 23:19:49: Execution Failed

18/09/2016 23:19:51: Execution finished.

When Script Succeeded:

19/09/2016 07:10:41: 4253 FillRate19:

19/09/2016 07:10:41: 4254 LOAD

19/09/2016 07:10:41: 4255 Fr.Company

19/09/2016 07:10:41: 4256 ,Fr.Wharehouse

19/09/2016 07:10:41: 4257 ,Fr.Order#

19/09/2016 07:10:41: 4258 ,Fr.OrderLine AS Fr.OrderLine

19/09/2016 07:10:41: 4259 ,Fr.OrderIndex

19/09/2016 07:10:41: 4260 ,Fr.Item#

19/09/2016 07:10:41: 4261 ,Fr.StockTransType

19/09/2016 07:10:41: 4262 ,Fr.TransIdentity

19/09/2016 07:10:41: 4263 ,Fr.TransDate

19/09/2016 07:10:41: 4264 ,Fr.MaxEntryDate

19/09/2016 07:10:41: 4265 ,MaxInvDate

19/09/2016 07:10:41: 4266 ,Fr.Source

19/09/2016 07:10:41: 4267 ,Sum(Fr.TransQty) AS Fr.SumTransQty

19/09/2016 07:10:41: 4268 FROM ..\..\Model-Sales\QVD\Tables_Modified\FillRate20.qvd (qvd)

19/09/2016 07:10:41: 4269 GROUP BY

19/09/2016 07:10:41: 4270 Fr.Company

19/09/2016 07:10:41: 4271 ,Fr.Wharehouse

19/09/2016 07:10:41: 4272 ,Fr.Order#

19/09/2016 07:10:41: 4273 ,Fr.OrderLine

19/09/2016 07:10:41: 4274 ,Fr.OrderIndex

19/09/2016 07:10:41: 4275 ,Fr.Item#

19/09/2016 07:10:41: 4276 ,Fr.StockTransType

19/09/2016 07:10:41: 4277 ,Fr.TransIdentity

19/09/2016 07:10:41: 4278 ,Fr.TransDate

19/09/2016 07:10:41: 4279 ,MaxInvDate

19/09/2016 07:10:41: 4280 ,Fr.MaxEntryDate

19/09/2016 07:10:41: 4281 ,Fr.Source

19/09/2016 07:10:41: 4282

19/09/2016 07:19:00: 13 fields found: Fr.Company, Fr.Wharehouse, Fr.Order#, Fr.OrderLine, Fr.OrderIndex, Fr.Item#, Fr.StockTransType, Fr.TransIdentity, Fr.TransDate, Fr.MaxEntryDate, MaxInvDate, Fr.Source, Fr.SumTransQty, 34,030,894 lines fetched

19/09/2016 07:21:04: 4283 STORE FillRate19

19/09/2016 07:21:04: 4284 INTO ..\..\Model-Sales\QVD\Tables_Modified\FillRate19.qvd (qvd)

19/09/2016 07:21:11: 4285 DROP TABLE FillRate19

The Script Itself:

////------

FillRate19:

LOAD

Fr.Company

,Fr.Wharehouse

,Fr.Order#

,Fr.OrderLine AS Fr.OrderLine // /100

,Fr.OrderIndex

,Fr.Item#

,Fr.StockTransType

,Fr.TransIdentity

,Fr.TransDate

,Fr.MaxEntryDate

,MaxInvDate

,Fr.Source

,Sum(Fr.TransQty) AS Fr.SumTransQty

FROM $(vTablesModified)FillRate20.qvd (qvd)

GROUP BY

Fr.Company

,Fr.Wharehouse

,Fr.Order#

,Fr.OrderLine

,Fr.OrderIndex

,Fr.Item#

,Fr.StockTransType

,Fr.TransIdentity

,Fr.TransDate

,MaxInvDate

,Fr.MaxEntryDate

,Fr.Source

;

STORE FillRate19

INTO $(vTablesModified)FillRate19.qvd (qvd);

DROP TABLE FillRate19;

//------

Re: General Script Error

Anil_Babu_Samineni — Mon, 19 Sep 2016 07:42:50 GMT

Try Like This

When Script Failed:

18/09/2016 23:12:10: 4253 FillRate19:

Qualify *,

Unqualify OrderLine;

18/09/2016 23:12:10: 4254 LOAD

18/09/2016 23:12:10: 4255 Fr.Company

18/09/2016 23:12:10: 4256 ,Fr.Wharehouse

18/09/2016 23:12:10: 4257 ,Fr.Order#

18/09/2016 23:12:10: 4258 ,Fr.OrderLine AS OrderLine //Fr.OrderLine

18/09/2016 23:12:10: 4259 ,Fr.OrderIndex

18/09/2016 23:12:10: 4260 ,Fr.Item#

18/09/2016 23:12:10: 4261 ,Fr.StockTransType

18/09/2016 23:12:10: 4262 ,Fr.TransIdentity

18/09/2016 23:12:10: 4263 ,Fr.TransDate

18/09/2016 23:12:10: 4264 ,Fr.MaxEntryDate

18/09/2016 23:12:10: 4265 ,MaxInvDate

18/09/2016 23:12:10: 4266 ,Fr.Source

18/09/2016 23:12:10: 4267 ,Sum(Fr.TransQty) AS Fr.SumTransQty

18/09/2016 23:12:10: 4268 FROM ..\..\Model-Sales\QVD\Tables_Modified\FillRate20.qvd (qvd)

18/09/2016 23:12:10: 4269 GROUP BY

18/09/2016 23:12:10: 4270 Fr.Company

18/09/2016 23:12:10: 4271 ,Fr.Wharehouse

18/09/2016 23:12:10: 4272 ,Fr.Order#

18/09/2016 23:12:10: 4273 ,Fr.OrderLine

18/09/2016 23:12:10: 4274 ,Fr.OrderIndex

18/09/2016 23:12:10: 4275 ,Fr.Item#

18/09/2016 23:12:10: 4276 ,Fr.StockTransType

18/09/2016 23:12:10: 4277 ,Fr.TransIdentity

18/09/2016 23:12:10: 4278 ,Fr.TransDate

18/09/2016 23:12:10: 4279 ,MaxInvDate

18/09/2016 23:12:10: 4280 ,Fr.MaxEntryDate

18/09/2016 23:12:10: 4281 ,Fr.Source

18/09/2016 23:12:10: 4282

18/09/2016 23:19:48: General Script Error

18/09/2016 23:19:49: Execution Failed

18/09/2016 23:19:51: Execution finished.

Re: General Script Error

Anonymous — Mon, 19 Sep 2016 07:46:57 GMT

Try putting a sleep(9000); before the STORE.

I know this sounds a bit obtuse, but I have often seen it sort random General Script Errors like this.

Re: General Script Error

shayraber — Mon, 19 Sep 2016 07:47:10 GMT

hi Anil,

can you, please, explain the logic behind your suggestion?

also, that table is in use later in the script, it will affect all other usage of that table

Re: General Script Error

shayraber — Mon, 19 Sep 2016 08:23:26 GMT

hi Marcus,

I've tried to check out the server usage model (for the RAM) and didn't see anything weird.

regarding the incremental load - it's not an extract process, but part of the data preparation process.

how can you do that group by in few separated parts?

Shay

Re: General Script Error

Peter_Cammaert — Mon, 19 Sep 2016 08:40:31 GMT

A Virus scanner intervening once in a while at unexpected moments?

Re: General Script Error

marcus_sommer — Mon, 19 Sep 2016 09:09:21 GMT

For an incremental load-approach it's not essential what kind of loading it is and if any transformations happens - important is only if the same records will be loaded again and again.

Whereby if I look on the answers from the others it seems that you had really rather an issue with the storing-command because that there is any conflicting between Qlik, the OS and maybe some other scanner. In single cases it's often helpful to restart the system but if it happens more often you need some workaround.

The from Bill mentioned sleep-statement might be helpful for this - especially if it's embedded within a small sub-routine which checked if the file is stored and if not do it again - I saw recently here such posting but couldn't find it at the moment. Therefore I'm nout sure if this included the use of ERRORMODE or not. But here you could find something to them and further approaches: Avoid General Script Error in STORE command.

- Marcus

Re: General Script Error

puttemans — Mon, 19 Sep 2016 09:20:22 GMT

I experience this also from time to time with some applications, and I can guarantee you it is not the script that contains an error. There have been 2 situations that would trigger this:

Option 1: from time to time, your server gets too much work, and if you are running this script at that particular time on it, it will stop working and give an error

Option 2: there is no space left anymore to write away the generated file. This will trigger the same behaviour

Regards,

Johan

Re: General Script Error

shayraber — Mon, 19 Sep 2016 10:48:41 GMT

Hi Marcus/Bill M.

Ill give it a try....

ill try to put some sleep before saving the QVD

Shay