topic Re: Circular References in QlikView

Circular References

raynac — Mon, 14 Dec 2015 17:23:19 GMT

I am 99.9% sure I know the answer to this, but I just need a few of the resident experts to sanity check me and say I am right before I take it to my company owner.

We have a table that contains clients, and clients are essentially the "hub" of a wheel-like data model. Clients can have reservations, scbookings and cmbookings. All three of these are stored in separate tables, and each of these tables is, essentially, like a spoke on that wheel.

Then each of these three tables can have folios attached.

There is no way to load the folio table only once, right? Because if I go from client to the three tables and then try and go from all three of those tables back into one single folio table, I'll get a circular reference. From the research I have done, the recommended method is to load that folio table three times, which is actually what I am doing, but the table is huge (many millions of records) for some of my users and loading it three times crashes their machines due to lack of resources, so we were trying to find a way to load it only once.

Any assistance is much appreciated.

Re: Circular References

Clever_Anjos — Mon, 14 Dec 2015 17:24:56 GMT

Use only one table table for reservations, scbookings and cmbookings, using concatenate

Re: Circular References

raynac — Mon, 14 Dec 2015 17:32:08 GMT

Hmmm....an interesting idea that I had not thought of!

What if the three tables are all vastly different, and need to be able to be distinct so that they can be queried separately? That won't work then, will it?

Re: Circular References

hemhund2016 — Mon, 14 Dec 2015 17:32:19 GMT

I would prefer using the Link table with the Client ID and reservations, scbookings and cmbookings.

This way you will have one table for Fact data and ideal Star schema.

https://community.qlik.com/blogs/qlikviewdesignblog/2013/05/17/concatenate-vs-link-table

Thanks,

hemanth.

Re: Circular References

Clever_Anjos — Mon, 14 Dec 2015 17:36:33 GMT

Use a field that enable you query apart

LOAD

'reservations' as type,

other fields

from reservations.qvd(qvd);

concatenate LOAD

'scbookings' as type,

other fields

from scbookings.qvd(qvd);

concatenate LOAD

'cmbookings' as type,

other fields

from cmbookings.qvd(qvd);

Re: Circular References

raynac — Mon, 14 Dec 2015 17:42:26 GMT

I am following you, I believe. In fact, I had something in mind where I hard coded a field in each table to be able to use as a selector to say "just show me the cmbookings records", which I think is what you are saying!

Thank you; I'll give this a go.

Re: Circular References

raynac — Mon, 14 Dec 2015 17:43:01 GMT

Thank you so much! I'll take a look at this article and see if I can make it work!