topic Re: How to remove circular reference in QlikView

How to remove circular reference

Anonymous — Wed, 27 Mar 2013 11:47:35 GMT

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

Re: How to remove circular reference

Anonymous — Thu, 04 Apr 2013 06:44:11 GMT

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

Re: How to remove circular reference

advait_thakur — Thu, 04 Apr 2013 08:12:11 GMT

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

Re: How to remove circular reference

Thu, 04 Apr 2013 09:36:16 GMT

Hi,

Please see the attached file, it may help you

Niranjan M.

Re: How to remove circular reference

Anonymous — Thu, 04 Apr 2013 11:36:23 GMT

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

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

Thanks,

Leni

Re: How to remove circular reference

Anonymous — Thu, 04 Apr 2013 11:42:27 GMT

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

Re: How to remove circular reference

Thu, 04 Apr 2013 12:25:28 GMT

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.

Re: How to remove circular reference

Anonymous — Thu, 04 Apr 2013 13:09:58 GMT

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

Re: How to remove circular reference

advait_thakur — Thu, 04 Apr 2013 13:13:48 GMT

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

Re: How to remove circular reference

Thu, 04 Apr 2013 15:54:56 GMT

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.

Re: How to remove circular reference

Anonymous — Fri, 05 Apr 2013 05:07:46 GMT

Thanks for your solution

But in my case I am not using expression. It is like I will be having a button to swap between different type of date.

Initially I will show the date from "yyy" table and will make selection on that date field and click on the button to change to the objects using date from "xxx" table. So in table yyy if I select 2013-01-27 date and change to the "xxx" table date, it will have a multi-box which will show the date range like "2013-01-15 - 2013-02-28".

To achieve this only I have used IntervalMatch, whether we can use anything else to achieve this??

I have attached a sample on how it will work in layout.

Thanks,

Leni Balakrishnan

Re: How to remove circular reference

advait_thakur — Fri, 05 Apr 2013 05:18:03 GMT

Hello Leni,

Still your data model is having Synthetic keys. (Please see the image which I had posted to your reply)

You can rename your fields of date which are coming from XXX and YYY table. Avoid the synthetic keys.

Yes, you can use Set Analysis for the same instead of intervalmatch. (Steve has mentioned in the above post).

Thanks

Advait

Re: How to remove circular reference

Fri, 05 Apr 2013 06:39:16 GMT

Hi,

Varify resource utilization, if ram usage is more, then try to drop tables that are no longer used in the script.

Niranjan M.

Re: How to remove circular reference

Anonymous — Fri, 05 Apr 2013 09:22:59 GMT

Hi Advait,

Can you help by modifying my sample using set analysis?? As I don't know how to use in my requirement.

Thanks in advance

Thanks,

Leni

Re: How to remove circular reference

Anonymous — Tue, 09 Apr 2013 09:13:35 GMT

I have achieved this requirement by using the expression suggested by steve.

Thanks steve.

I have attached the modified file.

Thanks all for the help.

~Leni