Link Table

[shamitshah]

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

[avinashelite]

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

[shamitshah]

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

[avinashelite]

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

[shamitshah]

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

[avinashelite · Accepted solution]

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;

[shamitshah]

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