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;
You can actually use the loop value with dollar sign expansion to dynamically change the variable name.
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_$(DBName)) ' ; 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;
