Avoiding Circular References

[#1]

Hey everybody!

I'm working on a new data model trying to improve the existing one and I'm having a bit of a Circular Reference problem.

This is part of my model, every time I load the "expenses_total_sheet" (I would load one for each year in my DB) I want to assign the information to a certain year (since the data in that table is a single row per year)...

However if i just give it a:

Year(MakeYear(2015)) as Year

I get a Circular Reference... how would you avoid that?

Thanks in advance!

[#2]

Iv'e tried adding a new field in "expenses_final" that would be called "expenses_total_year"

and having a corosponding field in "expenses_total_sheet".

This creates for me a Synthetic key which is working preety fine but I have seen some difference of opinions on using (and keeping) Synthetic keys.

What do you guys think?

[tresesco]

Try like:

2015  as  [Expense Year]

[tresesco]

If your data is not very big, synthetic keys would not be a problem.

[#5]

The DataModel I'm trying to reconstruct is around 250MB...

I hope i can get it down as much as possible but would you say that counts as big?

[jagan]

HI,

Rename Year with some other name like [Expense Year]

Year(MakeYear(2015)) as [Expense Year]

Hope this helps you.

Regards,

Jagan.

[#7]

That's what I did...

And then i added that "[Expense Year]" to the expenses_final table (using  - Year(Date) as [Expense Year])...

Resulting in a Synthetic key, so far it's working great but I am worried of  preformance issues,

What do you think?

[Peter_Cammaert]

This is a technique to get the expenses_total_sheet_YYYY data correctly connected to expense_final. I'm assuming that this is the right data model, as I have no information about what it should accomplish (and only part of it is visible):

1. When loading expense_final, create a composite key like:

:

  portfolio & '|' & Year(Date) AS ExpenseTotalKey,

:

2. When loading expense_total_sheet_YYYY, make sure that the year is available in a field or in the filename (if you load different years by using a LOAD * FROM [expense_total_sheet_*.abc]) or in a variable (if you use a FOR loop). Then rename the table to expense_total_sheet (it will contain all years, which makes it much simpler) and create a Key field like:

:

portfolio & '|$(vYearFromFile)' AS ExpenseTotalKey,

:

if you use a variable (and a FOR loop), or like:

:

portfolio & '|' & YearField AS ExpenseTotalKey,

:

if you use a field in the data source, or

:

portfolio & '|' & mid(FileName(), 21,4) AS ExpenseTotalKey,

:

if you need to lift the year from the file name. Note that the last technique must be adapted to your situation.

3. Then drop the portfolio field in one of the two tables, or you will get a synthetic key as well.

Best,

Peter

[jonas_rezende]

Hi, ShaharRo.

My suggestion would be to work with composite key, as example below:

//All years in expenses_total_sheet or one.

expenses_total_sheet:

Load

Autonumber(MakeYear(2015)&'_'&portfolio)   as %YearPortfolio

portfolio,

profit_percent,

maam,

partner_loan

[<tablename>];

expenses_final:

Load

Autonumber(MakeYear(2015)&'_'&portfolio)    as %YearPortfolio

expense_type,

Date,

portfolio,

expense_ammount,

expense_sum

[<tablename>].

I hope this helps!