Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Please share the log file
Hi,
Check whether the QVD file locked?
Regards,
Jagan.
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
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.
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;
//------
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.
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.
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