Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to remove the Syn key (see below) by creating a Link table. I think the link table should sit between the HISTORY and ProductBalances tables. Secondly the compound key required would require to include ProductID and RecordDate.
I am not sure how to create the compound key given that the RecordDate and ProductID are derived in the script (highlighted below).
Currently in the script the data is loaded as
HISTORY:
LOAD
"SKH_ID",
Date([SKH_RecordDate] + MakeDate(2000,1,1)) as RecordDate,
Year(AddMonths(Date([SKH_RecordDate] + MakeDate(2000,1,1)),6)) as FiscalRecordYear,
Year(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordYear,
Month(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordMonth,
MonthName(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordMonthYr,
'Q' & (mod(Ceil(Month(Date([SKH_RecordDate] + MakeDate(2000,1,1)))/3)+1,4)+1) as RecordQuarter,
/**/
"SKH_StockID",
"SKH_BaseStockID"as ProductID,
"SKH_BatchListID";
SQL SELECT *
FROM "SKH_StockHistory";
Any ideas on how I can remove the Syn table?
Thanks
Shamit
Try like this
HISTORY:
LOAD
"SKH_ID",
Date([SKH_RecordDate] + MakeDate(2000,1,1))as RecordDate,
Year(AddMonths(Date([SKH_RecordDate] + MakeDate(2000,1,1)),6)) as FiscalRecordYear,
Year(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordYear,
Month(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordMonth,
MonthName(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordMonthYr,
'Q' & (mod(Ceil(Month(Date([SKH_RecordDate] + MakeDate(2000,1,1)))/3)+1,4)+1) as RecordQuarter,
/**/
"SKH_StockID",
"SKH_BaseStockID"as ProductID,
Date([SKH_RecordDate] + MakeDate(2000,1,1))&'-'&"SKH_BaseStockID" as Key
"SKH_BatchListID",
"SKH_Lot",
"SKH_LocationsID",
"SKH_BinID",
"SKH_Quantity",
"SKH_OnHold",
"SKH_Source",
"SKH_URN",
"SKH_Reference",
"SKH_Username",
"SKH_IPAddr";
SQL SELECT *
FROM "SKH_StockHistory";
These were the steps to get the Product Balances :
// ---- A: Load all existing product balances
TempProductBalances:
Load
ProductID,
RecordDate,
SKH_Quantity
RESIDENT
HISTORY;
MinMaxDate:
Load Min(RecordDate) as MinDate, Max(RecordDate) as MaxDate resident TempProductBalances;
Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;
Drop Table MinMaxDate;
// ---- B: Create all combinations of product and date
TempProduct_x_Dates:
Load distinct ProductID Resident TempProductBalances;
Join (TempProduct_x_Dates)
Load Date(recno()+$(vMinDate)) as RecordDate Autogenerate vMaxDate - vMinDate;
// ---- C: Append missing records onto the product balance table
Concatenate (TempProductBalances)
Load * Where not Exists( ProductID_x_RecordDate );
Load ProductID, RecordDate,
ProductID & '|' & Num( RecordDate) as ProductID_x_RecordDate
Resident TempProduct_x_Dates ;
// ---- 😧 Create final product balance table. Propagate value from above record.
T_ProductBalances:
NoConcatenate
Load ProductID,
RecordDate,
If(ProductID=Peek(ProductID ),
RangeSum(SKH_Quantity,Peek(AccumulatedSKH_Quantity)),
RangeSum(SKH_Quantity)) as AccumulatedSKH_Quantity
Resident TempProductBalances
Order By ProductID,RecordDate; // so that above values can be propagated downwards
ProductBalances:
LOAD ProductID&'-'&RecordDate as Key,
AccumulatedSKH_Quantity
resident
T_ProductBalances;
// ---- E: Drop all temporary tables
Drop Tables T_ProductBalances,TempProduct_x_Dates, TempProductBalances;
In the Link table have 3 keys
RecordDate,
ProductID,
Key
Build a Composite key like this (Date([SKH_RecordDate] + MakeDate(2000,1,1))&'-'&"SKH_BaseStockID" as Key😞
HISTORY:
LOAD
"SKH_ID",
Date([SKH_RecordDate] + MakeDate(2000,1,1)) as RecordDate_HISTORY,
Year(AddMonths(Date([SKH_RecordDate] + MakeDate(2000,1,1)),6)) as FiscalRecordYear,
Year(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordYear,
Month(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordMonth,
MonthName(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordMonthYr,
'Q' & (mod(Ceil(Month(Date([SKH_RecordDate] + MakeDate(2000,1,1)))/3)+1,4)+1) as RecordQuarter,
/**/
"SKH_StockID",
"SKH_BaseStockID"as ProductID_HISTORY,
Date([SKH_RecordDate] + MakeDate(2000,1,1))&'-'&"SKH_BaseStockID" as Key
"SKH_BatchListID";
SQL SELECT *
FROM "SKH_StockHistory";
Build a similar key in the Product Balance table too and rename the ProductID and RecordDate with their respective table names
so this 2 tables should connect using Key field only
Hi Avinash,
Does the following affect your proposed solution?
1) Currently the ProductBalances table is derived from the fields in the HISTORY table.
2) By the changing the line in the HISTORY table to
"SKH_BaseStockID"as ProductID_HISTORY ,
would this break the link to the PRODUCTS and SALELINES tables? The common field is ProductID.
Thanks
Shamit
If your making resident of HISTORY to build the ProductBalances table then
LOAD KEY directly no need of the ProductID and RecordDate field...
If your not able to build the script share the complete script , so that I could guide you
Hi Avinash,
This the full script to load the HISTORY table and the ProductBalances Table:
HISTORY:
LOAD
"SKH_ID",
Date([SKH_RecordDate] + MakeDate(2000,1,1))as RecordDate,
Year(AddMonths(Date([SKH_RecordDate] + MakeDate(2000,1,1)),6)) as FiscalRecordYear,
Year(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordYear,
Month(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordMonth,
MonthName(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordMonthYr,
'Q' & (mod(Ceil(Month(Date([SKH_RecordDate] + MakeDate(2000,1,1)))/3)+1,4)+1) as RecordQuarter,
/**/
"SKH_StockID",
"SKH_BaseStockID"as ProductID,
"SKH_BatchListID",
"SKH_Lot",
"SKH_LocationsID",
"SKH_BinID",
"SKH_Quantity",
"SKH_OnHold",
"SKH_Source",
"SKH_URN",
"SKH_Reference",
"SKH_Username",
"SKH_IPAddr";
SQL SELECT *
FROM "SKH_StockHistory";
These were the steps to get the Product Balances :
// ---- A: Load all existing product balances
TempProductBalances:
Load ProductID,RecordDate,SKH_Quantity
RESIDENT
HISTORY;
MinMaxDate:
Load Min(RecordDate) as MinDate, Max(RecordDate) as MaxDate resident TempProductBalances;
Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;
Drop Table MinMaxDate;
// ---- B: Create all combinations of product and date
TempProduct_x_Dates:
Load distinct ProductID Resident TempProductBalances;
Join (TempProduct_x_Dates)
Load Date(recno()+$(vMinDate)) as RecordDate Autogenerate vMaxDate - vMinDate;
// ---- C: Append missing records onto the product balance table
Concatenate (TempProductBalances)
Load * Where not Exists( ProductID_x_RecordDate );
Load ProductID, RecordDate,
ProductID & '|' & Num( RecordDate) as ProductID_x_RecordDate
Resident TempProduct_x_Dates ;
// ---- 😧 Create final product balance table. Propagate value from above record.
ProductBalances:
NoConcatenate
Load ProductID,
RecordDate,
If(ProductID=Peek(ProductID ),
RangeSum(SKH_Quantity,Peek(AccumulatedSKH_Quantity)),
RangeSum(SKH_Quantity)) as AccumulatedSKH_Quantity
Resident TempProductBalances
Order By ProductID,RecordDate; // so that above values can be propagated downwards
// ---- E: Drop all temporary tables
Drop Table TempProduct_x_Dates, TempProductBalances;
Thanks
Shamit
Try like this
HISTORY:
LOAD
"SKH_ID",
Date([SKH_RecordDate] + MakeDate(2000,1,1))as RecordDate,
Year(AddMonths(Date([SKH_RecordDate] + MakeDate(2000,1,1)),6)) as FiscalRecordYear,
Year(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordYear,
Month(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordMonth,
MonthName(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordMonthYr,
'Q' & (mod(Ceil(Month(Date([SKH_RecordDate] + MakeDate(2000,1,1)))/3)+1,4)+1) as RecordQuarter,
/**/
"SKH_StockID",
"SKH_BaseStockID"as ProductID,
Date([SKH_RecordDate] + MakeDate(2000,1,1))&'-'&"SKH_BaseStockID" as Key
"SKH_BatchListID",
"SKH_Lot",
"SKH_LocationsID",
"SKH_BinID",
"SKH_Quantity",
"SKH_OnHold",
"SKH_Source",
"SKH_URN",
"SKH_Reference",
"SKH_Username",
"SKH_IPAddr";
SQL SELECT *
FROM "SKH_StockHistory";
These were the steps to get the Product Balances :
// ---- A: Load all existing product balances
TempProductBalances:
Load
ProductID,
RecordDate,
SKH_Quantity
RESIDENT
HISTORY;
MinMaxDate:
Load Min(RecordDate) as MinDate, Max(RecordDate) as MaxDate resident TempProductBalances;
Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;
Drop Table MinMaxDate;
// ---- B: Create all combinations of product and date
TempProduct_x_Dates:
Load distinct ProductID Resident TempProductBalances;
Join (TempProduct_x_Dates)
Load Date(recno()+$(vMinDate)) as RecordDate Autogenerate vMaxDate - vMinDate;
// ---- C: Append missing records onto the product balance table
Concatenate (TempProductBalances)
Load * Where not Exists( ProductID_x_RecordDate );
Load ProductID, RecordDate,
ProductID & '|' & Num( RecordDate) as ProductID_x_RecordDate
Resident TempProduct_x_Dates ;
// ---- 😧 Create final product balance table. Propagate value from above record.
T_ProductBalances:
NoConcatenate
Load ProductID,
RecordDate,
If(ProductID=Peek(ProductID ),
RangeSum(SKH_Quantity,Peek(AccumulatedSKH_Quantity)),
RangeSum(SKH_Quantity)) as AccumulatedSKH_Quantity
Resident TempProductBalances
Order By ProductID,RecordDate; // so that above values can be propagated downwards
ProductBalances:
LOAD ProductID&'-'&RecordDate as Key,
AccumulatedSKH_Quantity
resident
T_ProductBalances;
// ---- E: Drop all temporary tables
Drop Tables T_ProductBalances,TempProduct_x_Dates, TempProductBalances;
Hi Avinash,
That seems to work fine, except that the dates where there were no transactions are not coming through.
Before:
After:
In the above , 18 and 19 June is not coming through.
The Sync table is no longer there.
Appreciate your assistance.
Thanks
Shamit