Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm facing a problem executing a script and I hope someone could helpe me.
I've got 3 tables, Customers, Transactions and Travellers linked and I want to create a new field in Travellers as cust_name & '|' & cost_center due to cost_center value can be the same for different customers while in fact they are different. Then I will link that new field to a new cost_center dimension table.
My problem is
Customers
%Key_Trans_Customer
cust_name
Transactions
trans_id
%Key_Trans_Customer
%Key_Trans_Travellers
Travellers
%Key_Trans_Travellers
cost_centre
So, what I do is to create a table with the distinct values with corresponding cust_name and cost_centre values.
tmp_cust_trav:
NoConcatenate
load distinct
%Key_Trans_Travellers,
%Key_Trans_Customer
resident Transactions;
left join (tmp_cust_trav)
load distinct
%Key_Trans_Customer,
cust_name
resident Customer
where Exists( %Key_Trans_Customer);
left join (tmp_cust_trav)
load distinct
%Key_Trans_Travellers,
cost_centre
resident Travellers
where Exists( %Key_Trans_Travellers);
Then, I add the new field to Travellers table from the previous tmp table but the execution here uses more and more RAM as time goes by until it gets all server ram (more than 80Gb) which I think is not normal. Travellers table has 20 milion rows and tmp_cust_trav around 100K.
left join (Travellers)
load distinct
%Key_Trans_Customer,
cust_name & '|' & cost_centre as CostCenterGroupID
resident tmp_cust_trav
where Exists( %Key_Trans_Customer);
Any idea to solve it?
Thanks in advance
Hi Ruben, your last left join shouldn't be?:
left join (Travellers)
load distinct
%Key_Trans_Travellers,
cust_name & '|' & cost_centre as CostCenterGroupID...
Hi Ruben, your last left join shouldn't be?:
left join (Travellers)
load distinct
%Key_Trans_Travellers,
cust_name & '|' & cost_centre as CostCenterGroupID...
Shit you are right, what a mistake... 4 eyes better than 2!
Thanks!