Same Variable causing loops

[#1]

Hi,

I have 2 tables that both contain a variable of Month.

The problem is that when I bring both tables into the model I get a problem with loops.

To resolve this I have to named them Month1 & Month2 but this doesn't allow me to select month across all data.

I tried to create an inline table with Month1, Month2, Month to try and unite the 2 months but the same problem.

Has anyone had this problem

Thanks

David

[#2]

Hi,

Two tables causing the loop...? I doubt

Can you please post your Table viewer and your script here...?

- Sridhar

[#3]

Hi David

One method would be to create a link table that holds your month value, and each of the other tables will hold key values to that link table.

Table1:

LOAD Month AS Month1.............................

Table 2:

LOAD Month AS Month2............................

Table 3:

LOAD Month, Month AS Month1, Month AS Month2...................

Effectively then when you create your charts you will be including Month as a dimension, but not Month1 or Month2 which are then just key values.

Regards,

Nigel.

[Anonymous]

David,
See if your business logic allows you to concatenate these two tables into one.. If not, you need link table.

[#5]

Hi Srifhar,

There first Table below is when I bring in the MYFlat and DJFlat files which provide us with data on salaries we pay for staff working in retail stores. This appears on the right of the Synth table and on the left are files I have in our system that take the staff number called ConRef and the retail store number called Storeref and convert them to how we list the store and staff and then pull up extra reference data like the Area Manager for the store, the consultants name and number on our system etc. This loads fine and summarises the data we are getting from our retail partner.

We want to expand this to include internal data we receive for these retail staff which includes their productivity and sales from which we pay commission. We want to be able to assess the committment we have to a retail store and also how accurately the internal reporting processes are.

I therefore bring in the table MTD which links in via Cons No. (our staff number which we link to the ConRef) and Period but I get a loop.

I have heard of link tables but have no idea how they work.

David

[#6]

Hi David

I would start with your first picture by removing any Synthetic keys as these just add confusion to the model and are un-necessary, you would do this by renaming appropriate fields and/or concatenating fields into keys so that you have a single join reference for QlikView, it is good to remember that QlikView will automatically join using ANY field name that matches.

For example, your tables INL19D & ZAPCUSTCSV both have TL_Cust and Store No as fields, and this will automatically cause a synthetic key reference to be built.

You can work around this by creating your own key field such as

LOAD TL_Cust & StoreNo AS CustStoreKey FROM INL19D

LOAD TL_Cust & StoreNo AS CustStoreKey FROM ZAPCUSTCSV

This will remove the synthetic key, however, it will mean that you dont have access to the TL_Cust field and StoreNo field individually. You can of course overcome this problem by loading these as fields from just one of the tables.

LOAD TL_Cust & StoreNo AS CustStoreKey, TL_Cust AS TL_Cust_Ref, StoreNo AS StoreNoRef FROM INL19D

In the case of ConsNo, the solution here depends on what you want to achieve in your model, but this is causing a loop and must be resolved. The question here is what is your real model, does MTD link to ConsNo and then to ConRef? Does MTD link to ConRef and then to ConsNo? Does MTD link to both of those tables individually (say one might be one-to-one and the other might be one-to-many)?

In either of these cases you can create your own key field names in order to "force" qlikview to create the model you want to see.

On the right side of your picture, the answer is quite easy, simply create (as the example above) a contatenated field as a key reference such as LOAD StoreRef&ConRef&Period as StoreConPeriodKey.

I think once you have the first picture sorted out it will be much easier for you to bring in the additional table, and perhaps you might be able to get away without creating a link table. Whilst link tables are realtively easy to create I try to avoid them as much as possible because the distort the picture of the model I want to use.

Regards,

Nigel.