topic Help with link table in QlikView

Help with link table

madsgrathe — Fri, 14 Jan 2011 15:07:03 GMT

Hi everybody

I'm having problems with loading my application and from reading around on the forums, I think my problem is with too many synthetic keys. My problem is that the client wants pretty much everything in one application (like always...) and I'm forced to work with a lot of data at once.

I have at least 3 synthetic keys (when I load all the data I want in the application I actually have 4 syn keys, but I've had to reduce the number of additional tables just to get the script reload to finish) which are - in prioritized order - Date, Realm and Emailid.

Below is a picture of my table associations:

I believe I understand the principles of the Link Tables, but I don't know what I specifically need to add to my script in order to make it work...

I've tried the "automatic link table script" ( http://community.qlik.com/media/p/76744.aspx ) but that just slows my script reload to a virtual halt.

Can anyone help me? I'm in a bit of a pickle as I really need to get the application to work. Thank you so much for any assistance.

Kind regards,

Mads

Help with link table

madsgrathe — Fri, 14 Jan 2011 17:18:20 GMT

Here's the code I'm trying to use to link table, but it's not working...

LINK_TABLE:
load distinct
Realm AS RealmKey
Date AS DateKey
emailid AS emailidKey
RESIDENT billing_turnover_all;

CONCATENATE

load distinct
Realm AS RealmKey
Date AS DateKey
emailid AS emailidKey
RESIDENT email_session;

Have I totally misunderstood the concept?

Help with link table

Sat, 15 Jan 2011 18:39:03 GMT

Hi Mads,

Your concept is right . As you are using the Same Links for different tables its giving the synthetic keys ..

Resident the Above link table and use more alias for the same column and drop the link table ..

Regards,

Chakravarthy.

Help with link table

madsgrathe — Sun, 16 Jan 2011 16:49:16 GMT


Chakravarthy wrote:
Hi Mads,
Your concept is right . As you are using the Same Links for different tables its giving the synthetic keys ..
Resident the Above link table and use more alias for the same column and drop the link table ..
Regards,
Chakravarthy. <div></div>

Hi Chakravarthy

Thank you very much for your response. I'm glad that I'm not totally off base.

Is there any way you can show me an example of that in specific script code? I'm not a coder, so I this scripting business is all very new to me, but I'm slowly getting the hang of it 🙂

Here's a screenshot of the Script Execution window, so you can see synth keys that QV automatically generates:

Thank you again.

Best regards,

Mads

Help with link table

madsgrathe — Mon, 17 Jan 2011 12:51:21 GMT

bump

Help with link table

Mon, 17 Jan 2011 13:18:36 GMT

Hi Mads ,

Let me take the example of below code ..I have two tables taken from the different sheet from a single excel .

LOAD

UserName

Date

Code

FROM

(

ooxml, embedded labels, table is

Sheet1);

LOAD

Name

Date

Code

FROM

(

ooxml, embedded labels, table is

Sheet2);

In my first & second table I have two common fields i.e. Date & Code . This is the reason why I am getting the Sythetic Key .

Important thing to be known here is In QlikView if the Field names are same it joins automatcally . As my tables have two common fields with the same name it forms a synthetic key.

To eliminate this I am aliasing (Giving other name )the field . This is shown as below . I am creating the alias for date in the table 1

LOAD

UserName

Date

as Sample_Date

Code

FROM

(

ooxml, embedded labels, table is

Sheet1);

I have renamed the date as Sample_Date . By doing this I have only one field in common . So it only links with the Code.

So also make a alias for the field emailid or palm so that you can eliminate this synthetic key...

Regards,

Chakravarthy.

Help with link table

Mon, 17 Jan 2011 13:30:22 GMT

Hi Mads , One thing I have missed in the above post.

If you want to link it using both the columns i.e emailid and realm you can concatenate and make it as one field .

emailid&realm as Key .

This also eliminates the synthetic key .

Regards,

Suman.

Help with link table

Mon, 17 Jan 2011 13:30:50 GMT

Hi Mads , One thing I have missed in the above post.

If you want to link it using both the columns i.e emailid and realm you can concatenate and make it as one field .

emailid&realm as Key .

This also eliminates the synthetic key .

Regards,

Chakravrathy.