Help with link table

[madsgrathe]

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

[madsgrathe]

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?

[#3]

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.

[madsgrathe]

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

[madsgrathe]

bump

[#6]

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.

[#7]

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.

[#8]

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.