Incremental Load for multi-company tables

roisolberg — Tue, 03 Sep 2024 09:52:42 GMT

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;

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

Re: Incremental Load for multi-company tables

morgankejerhag — Wed, 12 Feb 2025 20:15:51 GMT

You can actually use the loop value with dollar sign expansion to dynamically change the variable name.