How to remove circular reference

[Anonymous]

Hi All,

Currently I am facing a problem in creating a datamodel.

I have two tables xxx and yyy, I am creating a date table using the date field from yyy.

In xxx table I have two fields which represents date like Datefrom and Dateto. Now I need to link xxx and yyy table using the key company as well need to do interval match between xxx and yyy as if I select date in yyy I need to show the ranging date in xxx using the Datefrom and Dateto fields.

In this situation I am getting a circular reference.

My actual requirement is,

If I select a Date from table "yyy", then I need to show the relevant date range (datefrom & dateto) from "xxx". As well as the fields taken from "xxx" should change according to any selection in the application.

Kindly suggest me a way to achieve this.

Thanks in advance

~Leni

[Anonymous]

Hi,

I have removed the circular reference using left join. I have attached the sample file of it (sample of my actual appln).

I have a clarification that. Will it create any perfomance issue if we have a datamodel as I have in the attached file?

The data I am using is of 10gb and it is taking more time in IntervalMatch step and after an hour the application got closed and asks like "Reload with old data" like this.

But it is working fine with less data.

Please share your views.

Thanks,

Leni

[advait_thakur]

Hello leni,

Will you please try this with alias method. In your case, Alias the date field coming from YYY table.

Ex: Date as Date_YYY

Kindly rename the field.

Thanks

Advait

[#4]

Hi,

Please see the attached file, it may help you

Niranjan M.

[Anonymous]

But if I rename it then my purpose will not work right?

If I select a Date from table "yyy", then I need to show the relevant date range (datefrom & dateto) from "xxx". As well as the fields taken from "xxx" should change according to any selection in the application.

I want my application to work as my attached file (it is in my previous discussion)

Thanks,

Leni

[Anonymous]

Thanks

In your application only two tables are there but in my requirement I have one more table where we have all date manipulation.

I need to achieve the same using that table also.

Thanks,

Leni

[#7]

You can also add third table like below:

XXX:
LOAD * INLINE [
    FROM, TO, Value
    01/04/2013, 05/04/2013, abc
];

YYY:
LOAD * INLINE [
    Date,company
    04/04/2013, X
];

IntervalMatch (Date) LOAD FROM, TO Resident XXX;

Dates:

LOAD
Date,
Year(Date) as Year,
Month(Date) As Month
Resident
YYY
;

Niranjan M.

[Anonymous]

in the above one the tables are joined only using Date.

But this is not my requirement.

I have removed the circular reference now. but now my requirement is if I reload the application with less data it is working fine but if I reload the application with whole data (i.e., 10gb data) it ends with a message like "Reload with old data".

In my above discussion I have attached a sample which has my logic used in my main application.

Now I need to know the reason why the application got closed and getting a message like  "Reload with old data" if I reload with full data?

Thanks for your time

~Leni

[advait_thakur]

Hello Leni,

  I have seen your data model for the sample application which you have posted.

It has a Synthetic Key for Date_from, Date_To and Key_Date (See the image below)  Please see $Syn 1 table.

Yes, it will make performance/data issue. Please avoid the synthetic keys by Qualify statement or by Alias method, it will also resolve the loop.

Thanks

Advait

[#10 · Accepted solution]

Only join table 'xxx' and 'yyy' on the company field.  Rename your date fields to xxx_date and yyy_date to avoid joining and creating a circular reference.  To associate the dates use a set statement like this.

sum({<yyy_date= {'>=$(=MIN(xxx_date))<=$(=Max(xxx_date)}>}Amount)

I used MIN and MAX in case there is a timestamp associated to the date so it will give you the entire day.

If no timestamp you could simply use this.

sum({<yyy_date= xxx_date>}Amount)

Hope this helps.