topic How to avoid circular references in QlikView

How to avoid circular references

Fri, 31 May 2013 11:56:33 GMT

Hello,

I have a problem setting up my data model. I have following tables.

MasterCalendar (Manually created a calendar of the past 3 years)

Items - ItemKey

Relation - RelantionKey

Orders - OrderDate (has to link with the mastercalendar)

- Relationkey (has to link with relation table)

- Itemkey (has to link with item table)

Offers - Offerdate (has to link with the mastercalendar)

- relationkey (has to link with relationtable)

- itemkey (has to link with item)

I can't create a datamodel which has no circular reference.

Anyone has an idea?

Thank you.

Re: How to avoid circular references

alexandros17 — Fri, 31 May 2013 11:59:30 GMT

Try to left join mastercalendar to Orders and Offers, in this way the table disappears ...

then if you can left join (or join) other tables in order to destroy circular references

Re: How to avoid circular references

Fri, 31 May 2013 12:03:51 GMT

Hi jens,

You could use two methods...either Concatenate or create a link table.

The simplest would be to concatenate your offers and orders tables together, You could add an addition source field to allow you to identify the two datasets.

The link table method would involve created a new table containing a concatenation of the common fields in the orders and offers table and then creating a composite key to reference back to the offers and orders tables.

Generally, I would suggest the concatenation method.

KR,

Re: How to avoid circular references

er_mohit — Fri, 31 May 2013 12:05:16 GMT

Using join,Left join for both or you can use alias means change the name of field

then you can remove that circular refrence

Re: How to avoid circular references

Fri, 31 May 2013 12:05:33 GMT

Hello,

Thank you for the quick reply.

The mastercalendar has several columns. Fulldate, Year, Month, Day, ...

If i leftjoin the mastercalendar to orders and offers. I join all these columns to both tables. Both if i want a filter all orders and offers for last year? How can i do this then?

Thank you

Re: How to avoid circular references

Gysbert_Wassenaar — Fri, 31 May 2013 12:08:07 GMT

Well, one way would be to concatenate the Orders and Offers tables into one table. You could also try creating a link table. Here's a simple example. This document may also be useful to you.

Re: How to avoid circular references

Fri, 31 May 2013 12:21:26 GMT

Hello all, thanks for the replies allready!.

Accualty i have 7 tables ( i said 5 because in essence it is the same i thought) There is also a shipnote and invoice table. Which relates the same as the order and offers tables.

We were using a linktable, but this was very slow and taking lots of memory for simple tasks. So I was finding a way to remove the linktable.

Is it still usefull to join the 4 tables then, considering about 500k to 1 million records in every table?

Thankyou!