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

[sasiparupudi1]

Please try

QUALIFY Budget_Cat;

Accounts:

load

  CompanyID&'-'&Budget_Cat as MainKey,

  AccountID as [Budget AccountID],

  Budget_Cat,

  CompanyID as [Budget CompanyID]

Inline

[

AccountID, Budget_Cat,CompanyID

];

Budget:

load

CompanyID&'-'&Budget_Cat as MainKey,

Date,Budget_Cat,CompanyID

Inline

[

Date,Budget_Cat,CompanyID

];

Transaction:

load AccountID,Debit,Credit, Date as TransactionDate,CompanyID

inline

[

AccountID,Debit,Credit, Date,CompanyID

];

hth

Sasi

[mustafaelryah]

Welcome    

    I tried to convert the date as formatted , but same values,

Account_Budget Calculated good because I made tested chart Account_Budget on dimension and on expression sum(Value) and it give me good view, but when I putted it together the linking seems it is not good !!.

all data with you, can you try to but it together ?

[alex_millan]

Please, have a look again, although is true that many values are calculated, not all of them are.

Go to Table viewer and right-click over the table 'Chart_Of_Accounts', a viewer mode would open and you can check that the field 'Account_Budget' is null for a lot of values. That field is needed to load the different values of your mapping table.

Check for example the Account_Budget value for %Company = '1-1000' or '1-4608'

[mustafaelryah]

Dear Alex,

Yes most of these null values is out of budget I will make it in group, also some null values need to re-arrange, I will arrange it and return to you.

With Regards.

[alex_millan]

Yes Mustafa, perfect!

note also that if you load transactions and budget separately, you won't find a single common value between those tables:

In transactions: all values begin with 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13 or 14

In Budget: all values begin with 1 or _

Regards

[mustafaelryah]

Dear Alex,

I rearranged it and I checked all of them with accounting department, replace the below code on the masters tab:

https://www.dropbox.com/s/iw8c3slo493gbuq/Masters%20Tab.txt?dl=0

after reload you will not find the non-values.

so what I can do now?,

Best Regards

[sasiparupudi1]

You can please try my script

[alex_millan]

Attached the file with a few modifications, now the mapping seems to work fine.

Nevertheless, as I told you, there's no common values for the field '#Budget' between the tables 'Budget' And 'Transactions'.

Check it out. Hope that helps.

Regards

[mustafaelryah]

Dear Alex,

I have personal edition so I can't open the file as below :

Can you attach the code it text file ,, or can you write what you changed.

Regards

[mustafaelryah]

Alex, you are right when I opened the transaction table from the viewer I find that the mapping not ok

-it brings the values of (%company) and I want the values of Account_Budget to be on the transaction table.

I need this column to be as 2_Revenues_31/01/2015 can I do that  via mapping?