topic Time Object and Circular Reference in QlikView

Time Object and Circular Reference

Mon, 18 Jul 2011 21:59:55 GMT

Hi folks,

I'm new to qlikview and love the tool!

I have been lurking about here and thought I would try and see if the community had any insight into an issue we have come upon.

Currently we are associating accounts and time to everything example:

Account_time

Ticket Revenue

This way when selecting an account and a time frame you will see the tickets for the month and revenue for the month.

This however requires every account to have a record for every possible time…

Is there a way to make time a table that joins not creating a circular reference in qlikview so we can see the following:

Account

Ticket Revenue

Time

The problem is the circular reference but it seems absurd to have a made up table with every account and every possible time combination in order to join subject areas together…

Thanks for any insight.

Time Object and Circular Reference

swuehl — Mon, 18 Jul 2011 23:09:50 GMT

Hi Mike,

sorry, I don't understand your data structure. Could you post a screenshot of your table view or at best post a small QlikView sample application illustrating your problem (you may add files to posts in advanced editor)?

Regards,

Stefan

Time Object and Circular Reference

Tue, 19 Jul 2011 04:02:00 GMT

Hi,

If possible try to post your data model.In otherhand you can give a try like concatnating key fields from any of two tables for example key fields from the tables Account_time and Ticket.

-Jai

Time Object and Circular Reference

Wed, 20 Jul 2011 13:12:36 GMT

Basically, our data model is one such as:

Table1

Account Number

Account_Time_Key

Account Name

Table2

Account_Time_Key

Revenue Type

Revenue Amount

Table3

Account_Time_Key

Ticket Number

Ticket Type

You see, instead of having a TIME table that can associate with every table, we have to put TIME in the Key, to create the associations.

We were looking to remove TIME from the Key and have every table associate through a table called TIME. So that with an ACCOUNT_KEY, each table will associate and TIME will just be another object to reference.

Time Object and Circular Reference

swuehl — Wed, 20 Jul 2011 13:47:25 GMT

Hi Mike,

I can't really see the circular reference in this model..

As I understand a circular reference, you have a linkage from table1 to table2, then from table2 to table3 and back from table 3 to table1.

For example:

Table1

Account Number

Account_Time_Key

....

Table2

Account_Time_Key

Revenue Type

....

Table3

Revenue Type

Account Number

....

I don't think it is a problem if three tables share one and only one key.

Maybe I misunderstand something completely.

But back to your question (you wanted to remove the time from the key):

What is preventing you from doing so?

I see account details, revenue details and ticket details.

It should / might be possible to link them by account ID only and put the time into the ticket table.

With QlikViews logic, it is still possible to select Account, Date range of tickets and build revenue from that.

Not sure if this helps, is it possible for you to post a small example QlikView application?

Regards,

Stefan