Link table Sales - Bugdet - Item table.

[#1]

i have 3 table:

Budget table, which contain

StoreNo, Date,  ItemCat, Budget Amount

Item Table.

Item No. ItemCat

Sales Trans Table:

Date, Store, Item No.

now, i'm trying to build a link table where i can see budget for all Item Cat even if there is no sales for this item on a day from a store.

any idea?

i tried with this model, as below, but i didnt work, as the key to link budget and Trans, is not happening.

this is what i did.

Stage1:

Load Distinct

  Store

  ,'Transaction' AS TableFlag

  ,ItemKey

  ,[Date]

  ,[Transaction No_]

Resident Transaction ;

Concatenate

load

  BudgetHFB as HFBNo,

  'Budget' AS TableFlag,

  Store,

  BudgetDate as Date

Resident Budget;

Concatenate

LOAD

  ItemKey

  ,'Item' AS TableFlag

  ,if(left([Home Furnishing Business No_],1)='0',right([Home Furnishing Business No_],1),[Home Furnishing Business No_]) as HFBNo

Resident MasterItemTable;

Link:

Load

  *

  ,Store & '-' & HFBNo & '|' &  Num(Date) AS BudgetKey

  ,Store & '|' & ItemKey & '|' &[Date]& '|' & [Transaction No_] AS TransactionKey,

  HFBNo & '|' & ItemKey as HFB_ItemKey

Resident Stage1;

DROP Table Stage1;

drop Field Store ,ItemKey ,[Date] ,[Transaction No_] from Transaction;

drop Field  BudgetHFB, Store, BudgetDate from Budget;

Drop field ItemKey from MasterItemTable;

thanks for your support.

[rubenmarin]

Hi Omar, if the MasterItemTable doesn't have any metric or many to many relationship that justifies having them in a separate table, joining them in the transactions table will give a simpler model where you can you join directly transactions and budget using Store, Category and Date as a key.

About the current script, if the rows are just concatenated there is no row that makes the relations, you'll need to join by the key fields, so the related composite keys ends in the same row.