topic Avoiding Circular References in QlikView

Avoiding Circular References

Thu, 14 Jan 2016 13:43:58 GMT

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!

Re: Avoiding Circular References

Fri, 15 Jan 2016 07:17:55 GMT

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?

Re: Avoiding Circular References

tresB — Fri, 15 Jan 2016 07:26:29 GMT

Try like:

2015 as [Expense Year]

Re: Avoiding Circular References

tresB — Fri, 15 Jan 2016 07:35:49 GMT

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

Re: Avoiding Circular References

Fri, 15 Jan 2016 07:39:50 GMT

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?

Re: Avoiding Circular References

jagan — Fri, 15 Jan 2016 07:53:50 GMT

HI,

Rename Year with some other name like [Expense Year]

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

Hope this helps you.

Regards,

Jagan.

Re: Avoiding Circular References

Fri, 15 Jan 2016 08:00:26 GMT

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?

Re: Avoiding Circular References

Peter_Cammaert — Fri, 15 Jan 2016 09:23:58 GMT

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

Re: Avoiding Circular References

jonas_rezende — Fri, 15 Jan 2016 12:42:09 GMT

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!