topic Re: Large files - Help in QlikView

Large files - Help

bazzaonline — Mon, 20 Oct 2014 08:08:04 GMT

Hello,

I am trying to upload large excel files into Qlikview but always get an error message 'out of virtual memory'.

I saved the excels as txt. and this seemed to work however when loading the script a piece of code in the script no longer works, I think it might be as the fields are no longer in date/time format.

Left Join

LOAD ClaimNumber,

Interval(ClosedDate-Notificationdate,'D') AS DaystoClose

Resident [MainData];

Left Join

LOAD ClaimNumber,

If (DaystoClose<0,'Open', If (DaystoClose<=1,'0 days', If (DaystoClose<=6,'1-5 days', If (DaystoClose<=15,'5-14 days', If (DaystoClose<=30,'15-29 days', If (DaystoClose<=60,'30-59 days', If (DaystoClose<=90, '60-89 days','90+ days'))))))) as DaystoCloseGroup

Resident [MainData];

Can anyone help?????

Thanks

Re: Large files - Help

Peter_Cammaert — Mon, 20 Oct 2014 08:14:46 GMT

Your script is too short to figure out where the problem(s) is located.

Out of Virtual Memory means that:

either your JOIN is exploding your initial table (bad or NULL-ClaimNumber may lead to a catersian product e.g. JOIN everything to everything)
or you simply don't have enough memory in your development machine for the job at hand.

Best,

Peter

Re: Large files - Help

Peter_Cammaert — Mon, 20 Oct 2014 08:17:40 GMT

To check what is happenning, use the FIRST n - prefix to load a limited subset of records, or start the debugger, enable the "Limited Load" option and enter the numer of records you want to test with.

Peter

Re: Large files - Help

bazzaonline — Mon, 20 Oct 2014 08:21:10 GMT

Hello,

Appreciate your time, I have loaded and run the script perfectly when downloading direct from a server but this is no longer an option.

I then attempted to use excel as the data source which gave me the virtual memory issue.

To overcome that I saved the excel as txt. which allows it to load no issue but the join elements then no longer work. Hence my thoughts it must be the way the date formats are being translated??

Re: Large files - Help

Peter_Cammaert — Mon, 20 Oct 2014 08:58:24 GMT

Could be, but for me that's only guesswork.

Do the test with a limited LOAD, and check the contents of the Date Fields that are used in these JOINs. And do check what exactly is the end result of the JOINs, meaning: "what does your table contain after the JOINs are done".

Peter