Should I use the qvc.LinkTable subroutine for my synthetic key case?

[cheburashka]

Hello rwunderlich‌,

Am I correct that the qvc.LinkTable subroutine handles only synthetic key cases where each field of the synthetic key exists in each table that is connected to the link table?

I tried to solve my synthetic key case (see below) using the following calls:

CALL Qvc.LinkTable('LinkTable', 'UCC_CONTAINER', 'CONTAINERID');
CALL Qvc.LinkTable('LinkTable', 'UCC_CONTAINERURL', 'CONTAINERID, URLINDEX');
CALL Qvc.LinkTable('LinkTable', 'UCC_ENVELOPE', 'CONTAINERID, ENVELOPEID');
CALL Qvc.LinkTable('LinkTable', 'UCC_CONTACT', 'CONTAINERID, ENVELOPEID, URLINDEX');
CALL Qvc.LinkTable('LinkTable', 'UCC_RESPONSE', 'CONTAINERID, ENVELOPEID, URLINDEX');

It did give me a nice datamodel.

Because the datamodel behaved in a strange way I figured out that I interpreted the functionality of the qvc.LinkTable wrong.

Maybe the linktable subroutine could be changed to handle more complex cases?

My input would look like:

LinkTableInput:
LOAD * INLINE [
TableName, KeyFields
UCC_CONTAINER, 'CONTAINERID' 
UCC_CONTAINERURL 'CONTAINERID, URLINDEX'
UCC_ENVELOPE, 'CONTAINERID, ENVELOPEID'
UCC_CONTACT, 'CONTAINERID, ENVELOPEID, URLINDEX'
UCC_RESPONSE, 'CONTAINERID, ENVELOPEID, URLINDEX'
];

CALL Qvc.LinkTable('LinkTableX', 'LinkTableInput', 'CONTAINERID, ENVELOPEID, URLINDEX');

, KR Koen

[rwunderlich]

On the face of it, it looks like it should be ok. But I would like to see an example. Can you upload an example qvw?

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[cheburashka]

Hello Rob,

I think I found the issue.

An example case is attached but I think the image below summarizes the issue well.

Maybe a separator should be added when constructing the %LinkTableKey?

,Thanks for taking a look at it, KR Koen

[rwunderlich]

I see the issue. But I don't think a separator would help. In this case, the 3rd call to LinkTable has no knowledge of the "missing" KeyField2.

I see you've opened an issue. Let me think about the best way to handle this. But let me confirm: you don't want KeyField2 to associate with KeyField3, correct?

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[cheburashka]

Hello,

Correct, KeyField2 should not associate with keyField3.

They sometimes have the same values but they represent different entities/concepts.

The 3rd call indeed does not have knowledge of the previous call.

That's why I had the idea posted above, where I do one call of the subroutine with all knowledge about the linktable available in that one call.

,KR Koen

[cheburashka]

Hello,

The issue of the small example hereabove seems to be fixed with the qvc script adjustment. But after enlarging my example it seems qvc does not create the linktable that I would expect. When making selections the qvc created linktable cuts of parts of the model that should be in the selection set.

In the new example I compare 3 cases

-  a linktable created with joins

- a linktable created with a where clause( Not exists)

- a linktable created with qvc.linktable subroutine

Notes can be found in the bookmarks of the qvw.

,KR Koen