Hi,
Our ERP includes a few different companies (managed under our enterprise), so basically each table is loaded 8 times ( as we have 8 companies) using the following script that concatenates between the companies and differs by the DBname - the company code so in the end, i have single table that includes all.
For Each DBName in 'Company1', 'Company2', 'Company3', 'Company4' , 'Company5', 'Company6', 'Company7', 'Company8'
ORDERS:
LOAD *,
'$(DBName)' as Company_SalesOrder,
ORD & '|' & '$(DBName)' as Key_SalesOrderID_Company
;
SQL SELECT *
FROM $(DBName).dbo.ORDERS;
Next
and for storing it continues to the following:
For i = 0 to NoOfTables()-1
LET vTabNam = TableName($(i));
Store $(vTabNam) into $(vPriorityDB)$(vTabNam).qvd] (qvd);
Next i
LET vTablesNo = NoOfTables();
For i=1 to $(vTablesNo)
let vTabNam = TableName(0);
drop table [$(vTabNam)];
Next i
now, for my question:
I want to change my load to an Incremental load, but i haven't found any documentation that suits for a multi-company table
basically i think the main issue for me, is when trying to remove deleted lines as i need to Inner join with the original table - but it will only take 1 company
my script is as follow:
Let ThisExecTime = Now( );
For Each DBName in 'a101209','a090212'
ORDERS:
LOAD *,
'$(DBName)' as Company_SalesOrder,
ORD & '|' & '$(DBName)' as Key_SalesOrderID_Company,
ORD & '|' & '$(DBName)' as Key_SalesOrder_IncrementKey
where ModificationTime>='$(LastExecTime) '
;
SQL SELECT *,
system.dbo.tabula_dateconvert($(DBName).dbo.ORDERS.UDATE) AS ModificationTime
FROM $(DBName).dbo.ORDERS;
next
Concatenate
LOAD * FROM [lib://Incremental (enercon_roi.solberg)/ORDERS.qvd](qvd)
WHERE NOT EXISTS(Key_SalesOrder_IncrementKey);
//this will only take 1 table - according to the DBName
Inner Join
LOAD *,
ORD & '|' & '$(DBName)' as Key_SalesOrder_IncrementKey
;
SQL SELECT ORD FROM $(DBName).dbo.ORDERS;
If ScriptErrorCount = 0 then
Let LastExecTime = ThisExecTime;
End If
Any ideas?
Thanks,
Roi
