multiple keys

[#1]

Hi,

I'm having a problem.

My DB contains many tables and I am trying to connect three of them together using keys.

I'm trying to avoid synthetic keys so I've created my own keys.

When I'm doing it with the first two tables everything works great.

The problem is that the third table should be connected to the first one using other fields as ID then those who used to connect the first two and the moment that I am making the new key and try to connect between them I am getting lots of loops.

Those are the two first tables with my key (i droped all the non-relevant fields and tables.

Supply:

LOAD

CustomerNumber as _CustomerNumber,

SupplyItem&'|'&SupplyCustomerOrder as OrderKey,

SupplyCustomerOrder as _SupplyCustomerOrder,

SupplyDate as SupplyDate,

SupplyItem as _SupplyItem,

CustomerNumber&'|'&SupplyItem&'|'&Year(SupplyDate)&Num(Month(SupplyDate),00) as MainKey,

Year(SupplyDate)&Num(Month(SupplyDate),00) as _SupplyYYYYmm,

SupplyQty,

CustomerName,

CustomerType

From $(QvdPath)SupplyWithCustomers.qvd (qvd);

LOAD

BKSEL3,

BKSEL4,

BKSEL5,

BKSELA,

Customer as _SelfUseCustomerNumber,

Item as _SelfItem,

Customer&'|'&Item&'|'&YyyyMm as MainKey,

YyyyMm as _SelfUseYYYYmm,

MakeDate(Left(YyyyMm,4),Right(YyyyMm,2)) as SelfUseDate

FROM $(QvdPath)SelfUse.qvd (qvd);

The Key:

Keys:

LOAD DISTINCT

MainKey,

_SupplyYYYYmm AS _YYYYmm,

_SupplyItem AS Item,

_CustomerNumber AS CustomerNumber

RESIDENT Supply;

LOAD DISTINCT

MainKey,

_SelfUseYYYYmm AS _YYYYmm,

_SelfItem AS Item,

_SelfUseCustomerNumber AS CustomerNumber

RESIDENT SelfUse;

And this is the problematic table and key:

Orders:

LOAD

CustomerOrder as _CustomerOrder,

CustomerOrderCustomer as _CustomerOrderItem,

CustomerOrderItem&'|'&CustomerOrder as OrderKey,

CustomerOrderDate,

OrderStatus;

SQL SELECT *

FROM CustomerOrder.CustomerOrderLines;

Keys2:

LOAD DISTINCT

OrderKey,

_SupplyItem AS Item,

_SupplyCustomerOrder AS OrderNumber

RESIDENT Supply;

LOAD DISTINCT

OrderKey,

_CustomerOrderItem AS Item,

_CustomerOrder AS OrderNumber

RESIDENT Orders;

I will be grateful for any help.

Ron

[johnw]

Have you considered NOT avoiding synthetic keys? If all you're doing is trying to manually build the same compound keys that QlikView would build automatically if you simply named fields the same across your tables, you're probably just creating extra trouble for yourself for no benefit. So the first thing I'd try is just naming fields the same when they're the same thing, and letting QlikView build synthetic keys. If it doesn't work, then I suspect you have deeper data model issues, and I'll need to dig in more.

[#3]

Hi John,

First - what i've been told(when i just started with QV) is to try to avoid synthetic keys as much as I can. isn't it true ?

I've tried your suggestion, but it doesn't work. I'm getting a virtual memory error , then QV frooze and finally I am getting the message:

"Excution of script failed. Reload old data ?"

[#4]

This is with only one key

[johnw]

Ron wrote:what i've been told(when i just started with QV) is to try to avoid synthetic keys as much as I can. isn't it true ?

To the best of my knowledge, while most everyone seems to "know" and repeat this, it isn't true. See the below thread:

http://community.qlik.com/forums/t/31028.aspx

However, undesirable synthetic keys DO often arrise as a result of data model problems. And removing the synthetic keys often simultaneously fixes the underlying data model problems. The synthetic keys weren't to blame, but they can be hints that you're doing something wrong. It's possible that underlying data model problems are causing your synthetic key, and that it shouldn't be there, and therefore my suggestion was a bad one.

I'll take a closer look at your script when I get a little time. I'm rather pressed for time this week, unfortunately. If you want to help things along, is it possible to post an example of the problem, stripped down to inline loads and only the most important fields, with just a minimum of data? That would let me test possible solutions.

Or hopefully someone with a little more time can take a look for you sooner than I can.

[#6]

Hi John,

I just saw your answer (I was on holiday till yesterday).

I didn't understand - what you ment by "stripped down to inline loads " ?

[syed_muzammil]

Hi Ron,

I think by "stripped down to inline loads " John means load some dummy data using Inline for your main feilds and post it here . Btw, why don't you try joining any two tables which are causing loops using the key feild between them.

[johnw]

Yeah, that's what I meant. And sorry, but I haven't been able to look at this yet. I'm still swamped at work. Anyone else want to figure this one out?

[#9]

Hi Syed,

I've joined all three tables (like you offered) and it's look great ! I can't believe that it was so simple.

but just for my knowledge - how come QV know to join the tables with simple join between them and it doesn't work when i'm creating my own keys ? isn't that the same action ?

Thanks alot to you and to John for all your help

Supply:

LOAD

CustomerNumber,

SupplyCustomerOrder as CustomerOrder,

SupplyDate,

SupplyItem as Item,

Year(SupplyDate)&Num(Month(SupplyDate),00) as YYYYmm,

SupplyQty,

CustomerName,

CustomerType

From $(QvdPath)SupplyWithCustomers.qvd (qvd);

Join

LOAD

BKSEL4,

Customer as CustomerNumber,

Item as Item,

YyyyMm as YYYYmm,

MakeDate(Left(YyyyMm,4),Right(YyyyMm,2)) as SelfUseDate

FROM $(QvdPath)SelfUse.qvd (qvd);

Join

LOAD

CustomerOrder,

CustomerOrderItem as Item,

CustomerOrderDate,

OrderStatus;

SQL SELECT * FROM CustomerOrder.CustomerOrderLines;