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

[mohammadkhatimi]

Hie...

Try to do by link table...

PFA how to do link table jst go through it...

Hope this will helps you...!!!

Regards,

Mohammad

[sujeetsingh]

I think you should go through all the posts .

[mustafaelryah]

Here below from my real code:

Account_Budget is a (Budget_Cat)

account is a (AccountID)

company is (CompanyID)

when I run it I faced an error that the account field is not existing  and also company is not exist.

[mustafaelryah]

Also this error happened when I tried the load of load.

[alex_millan]

Hi again,

Are you loading the table 'Chart_Of_Accounts' before to try to load the map table, aren't you?

And have you checked that the name of the fields that you load in the map table matches perfectly (case sensitive) the fields in 'Chart_Of_Accounts'?

Also, it would be of great help if you can attach your script (or only the part relative to these tables) to check if you have any inconsistence.

[mustafaelryah]

Thaanks a lot , I find that I used the company and account to create a key already like a  below

company&'-'&account as %Company,

I load it again and worked successfully both of them load and map

and the linking now it seems is good,

but the results on chart confused me , I will send you the data to see it

[mustafaelryah]

Dear Alex ,

          Kindly find below here the data and the qv file

Dropbox - QV.rar

Can you check the budget chart, After I linked the actual with budget in table , I can't bring it both together in one chart to compare it .

Regards

[alex_millan]

Hi Mustafa,

Give me some time to have a look deeply but, at first sight, I can tell you that the data in the key-field #Budget is different in the two tables:

- the part corresponding to the date doesn't have the same format in the different tables

- the mapping is not working fine

I'll take a look later and come back on this in a while

[mustafaelryah]

Dear Alex,

     I appreciate your strong help to me, I do the linking for 3 days and I am so confused. Take your time and I am waiting for you.

Regards.

[alex_millan]

Hi again,

To get the date format aligned for the composite-key, change the script for the table 'Budget' to:

LOAD

Company & '_' &  Account_budget & '_' & Date(date,'DD/MM/YYYY') As #Budget,

Company, Account_budget, Value, date

FROM $(vPath)\BI Budget Database.xlsx(ooxml, embedded labels, table is Budget);

Also, I was trying to validate the table 'Chart_Of_Accounts' and it seems to be a problem with the field 'Account_Budget' as it's not being calculated. You must check it, that's a beast of a nested conditional

Possibly, when you have this field properly calculated, the mapping would work fine.

Once you solve it, let me know it you keep having problems with the mapping part.

Cheers!