Tables Structure

[mustafaelryah]

Dear All,

I have 3 tables:

1-Accounts

     1.1 AccountID

     1.2 (formula (AccountID)) as Budget_Cat..... # Calculated Column from the above one

     1.3 CompanyID

2-Buget

     2.1 Date

     2.2 Budget_Cat

     2.3 CompanyID

3.Transaction

     3.1 AccountID

     3.2 Debit

     3.3 Credit

     3.4 Date

     3.5 Company ID

I need to link  the Budget table with the Account table  (Budget_Cat, CompanyID) and Transaction table  (CompanyID, Date)

without looping or Syntactic keys

# note that the Budget_Cat is calculated field.

Regards

[sujeetsingh]

just take help of Composite keys

This will help you

how to create composite key

Please follow Henric blog for some more technical stuffs

Synthetic Keys

[mustafaelryah]

composite key is very helpful

so I can make one composite key on Budget Tabel CompanyID+Date+Budget Cat as Composite Field

but I use calculated column on the Account table ,

can I read the calculated column (Budget_Cat) on Transaction table to link it with the (date, companyID) ?

so I can put CompanyID+Date+BudgetCat as Compostite key

My main problem that I don't have the BudgetCat on Transaction table, can I move it using mapping?

[alex_millan]

To use your calculated field in your composite key, use precedent load

LOAD

CompanyID & Date & Budget_Cat As Key1,

---

;

LOAD

CompanyID,

Budget_Cat,

...

It's like doing a LOAD from your previous LOAD

And yes, you can do a mapping load from a resident table, for example:

Mapping LOAD Budget_Cat, CompanyID Resident YourTable;

HTH

Regards

[#5]

Hi. You must use the for one or two tables construction like this.

Accounts:

Select * from Accounts;

Loosen table Accounts;

Try it

[Anonymous]

Hi Mustafa,

Try:

Accounts:

LOAD

Budget_Cat & CompanyID as BudgetCompanyID,

    AccountID as Acc_AccountID,

    (Calc(AccountID)) as Acc_Budget_Cat,

    CompanyID as Acc_CompanyID

    From ....;

Budget:

LOAD

     Budget_Cat & CompanyID as BudgetCompanyID,

     CompanyID & Date as DateCompanyID,

     Date,    

     Budget_Cat as Bud_Budget_Cat,

     CompanyID as Bud_CompanyID

From ....;

Transaction:

LOAD

      CompanyID & Date as DateCompanyID,

      AccountID  as Tran_AccountID,

      Debit,

      Credit,

      Date as Tran_Date,

      CompanyID as Tran_CompanyID

From ....;

Regards

Neetha

[mustafaelryah]

It's nice solution thank you a lot

but I have a few complication ( Day field is on another table (transaction )) ?!

how can I do that

on Transaction table I have companyID and Date but I miss BudgetCat?

can I read the Budget_Cat on Transaction table? via mapping or something else? 

[alex_millan]

Yes Mustafa,

for example, if you have previously load BudgetCat and AccountID on your Accounts table (assuming there's only one BudgetCat for each AccountID), it would be like:

Accounts: LOAD AccountID, BudgetCat, CompanyID,... from ...;

Temp_Budget_Cat: Mapping LOAD BudgetCat, CompanyID Resident Accounts;

Transactions:

LOAD AccountID, ApplyMap('Temp_Budget_Cat',AccountID,'N/D') As BudgetCat, ....;

LOAD AccountID, Debit, Credit... from ....

Note that:

first, you load the Accounts data in order to have in memory the values for BudgetCat

second, you load a map table for these values

utterly, you use the map table in a precedent load when loading the transactions data

HTH

Regards

[mustafaelryah]

My Dear,

     Your solution is very good, but I have already link between transactions and accounts and if I applied the double link you suggest I will fall on the loop !!.

[mustafaelryah]

I will try to do it, and I will give you the feedback my dear.