Link Tables

[#1]

Hello,

I have a fairy simple data structure, with 2 fact tables ( Employee_Key & Transactions) and a few dimension tables. I would like to link both fact tables to my Date dimension table, but when I do that, I get a loop error.

I created a Link table - created a " Key" field in the Transactions table, composed of TicketId & Date. The "Key" field in the Employee_Key table is basically the same thing, but composed of different fields. The 2 fact tables do not have any common fields tought, only Date.

After running the script, the Link table is created, but the loop is still there.

First pic shows the data structure before the Link table, and the second picture is the data structure after I implemented the Link table.

Any thoughts on what I am doing wrong?

Thanks

[#2]

I don't think the link table is really helping, since both fact tables link to that, causing another loop. I think you need two Date_Tables, Employee_Date_Table and Transaction_Date_Table. You don't really want the two date fields linked in any way, so link them to separate tables.

We have a big calendar table like this and we end up linking a few fields to the date table to take advantage of the other fields (Month, Year, etc). We create multiple instances of the date table and precede the field names with something to identify which field they are referring to. Employee Week Ending Date, Transaction Month Name, etc...

[#3]

I want to avoid the 2 Date tables because I want to have one list box for Date that would work for the whole application, so that if I am selecting a specific month for Transactions, that month would also be filtered for the Employee fact table etc.

Any other way to accomplish this or the 2 Date tables is the way to go?

Thanks

[johnw]

I suspect that two date tables is the way to go, but it's not the only possible solution. To figure out what to do, you have to have a good understanding of what QlikView is doing, and why what it does can't work with data loops. When you make a selection, QlikView must find the set of all data consistent with that selection. A data loop can make this impossible. Let's make a simple one as an example:

Table1:
LOAD * INLINE [
A, B
1, 2
];

Table2:
LOAD * INLINE [
B, C
2, 3
];

Table3:
LOAD * INLINE [
C, A
3, 4
];

What happens if you select A = 1? QlikView traces it all the way through your data model. A = 1 gives you B = 2, which gives you C = 3, which gives you A = 4. Your starting point was consistent with the selection, but your ending point is not. So this set of records is both consistent and inconsistent with your selections. It should be both included and excluded. Your data is inconsistent, and QlikView doesn't know what to do with it. So instead, it forcefully breaks one of the links, which you see as the loop warning and the dashed arrows.

Your data model is just like this one, just with several more tables in between. With a data structure like this, it is POSSIBLE to have inconsistent data. That needs to be fixed. There are several possibilities:

1. If your actual data is never inconsistent, you can actually REMOVE one of the Date fields - it can always be established from the other Date.
2. If your data is inconsistent, you can make two different date fields as suggested. That's like making an A1 field and an A2 field in the structure above. No more loop.
3. You can make two different fields out of ANY field connecting two tables together anywhere in the loop.
4. You can make more radical changes to your data model to resolve the inconsistency, such as combining the employee and transaction tables (this is not a suggestion, just an example). I can't really make suggestions here because I'm not familiar with your data.

[Anonymous]

Maria,
You can use one Date table which is not linked to the data model - a logical island. "Connect" in expressions where needed.
Warning - if the data amount is large, it may be not good for perfromance.

[pover]

Maria,

If you want Employee_Key and Transaction to share the same date then maybe it would be useful to think of the Employee_Key table as some kind of transactional table, too. Actully you even mention it as a Employee fact table, so I think you could combine the Employee_Key and Transactions tables using the Concatenate function in QlikView.

In doing so, try to reuse as many columns between the 2 tables as possible. Sometimes this takes a little imagination. For example the "Owner" of the Transactions table could also contain the same data as the "Emp_Id 1" field when you combine the 2 tables. Obviously the Date column would be the same and I recommend creating an additional column that contains a Flag that tells you from which table the row originally came from. For example, 'E' for Employees and 'T' for Transactions.

The only tricky thing would be to have the Clients table link directly to the combined Transaction and Employee_Key table. You'll have to use an if and an applymap or join to make this happen.

In that way, you'll have one date field and a simplified data model.

In my opinion, this manner of doing things has worked alot better than using Link Tables even if it might take up a little more memory to run.

Regards.

[#7]

Thank you all for your input and the different solutions provided. I tried implementing all of them, which was a great learning experience.

The Date Island seems to be the way I would go about it (data volume is not big) , even though it does make writting the expressions a bit more difficult especially if it uses Set Analysis as well.

Thanks again, Maria