Problem with reload - out of virtual memory. Is there any way to do this

[#1]

I've got a lot of datas. And I think that this is the problem.

First table contains sales datas from 7 factories, datas about markets (domestic, export, intrnal group, external group), datas about salesman, budget datas. This table contains sth about 400 000 rows.

Now I need to concatenate Sales segments to the customers. I do this by the excell - I join this by the excell to to this table. Everything OK. But the second step is changing some segments.

Segment:
load
[Client number],
[BU],
[Factory],
[Sales person (Client)],
if([Factory]='NAM', 'FIRE',if(([BU]='FIRE' and [Factory]<>'NAM'), 'PROJEKTY',
if(([Client number]='170013' and
 [Factory]='PRU'), 'PROJEKTY',[BU]))) as [Business Unit],

 if(([Client number]='170013' and [Factory]='PRU'), 'Jan Kowalski',
 [Sales person (Client)]) as [Handlowiec]
resident Sales;

This step is correct.

But next I need to join budget to new segments. And here is the problem. For doing this I need to join table segment to table Sales

Left join (Sales)
load
[Business Unit],
[Handlowiec],
[Factory],
[Client number]
resident Segment;

My QV can't do this - out of virtual memory.

Help me. We can't boost the memory. Is ther eany way to rebuild the script

[#2]

In some cases, it is mandatory to analyze the left join specially when there is a huge amount of data.Some clues to make it work:

- You have many keys used to link the two tables, you should use only one primary key ( use autonumberhash128 funtion to create one ?)

- I'm afraind that you have null values in some cases in Sales table, this can also make strange behavior with joins...

- Is it important to make a left join ? ( maybe a stupid question but...)

- Did you try to run a Right join on Sales based on Segment ?

- In some cases running a LOAD Distinct can limit the number of rows in the Left join ( check that you don't lose data !)

I hope that it will help you.

Rgds,

Sébastien.

[#3]

I agree - it is the multiple keys between 'Sales' and 'Segment' that is causing the key. Multiple fields with 400 000 rows per table means the left join is eating resources. Use autonumberhash128 to create a single key value between the tables.

Regards,

Gordon