Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

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

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');

Datamodel with synthetic keys.PNG

It did give me a nice datamodel.

Datamodel after using Qvc.LinktTable incorrectly.PNG

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

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

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

Highlighted
Creator III
Creator III

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

Hello Rob,

I think I found the issue.

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

LinkTable key issue+solution.PNG

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

,Thanks for taking a look at it, KR Koen

Highlighted
MVP & Luminary
MVP & Luminary

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

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

Highlighted
Creator III
Creator III

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

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

Highlighted
Creator III
Creator III

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

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