topic Need to make Primary key in QlikView

Need to make Primary key

Wed, 25 Nov 2020 16:16:04 GMT

I'm a beginner to qlikview. Have loaded three xlsx file in QVW document, all the three xlsx file have one common field.

However, it is not seen as Primary key. Also, in the Table viewer, fields like '$Syn1', '$Syn3' are showing

How to remove this 'Syn' field and how to make the common field as primary key

Re: Need to make Primary key

oknotsen — Wed, 20 Jul 2016 09:38:36 GMT

In Qlik, there is no such thing as a "primary key". There are fields and they are related. There is no technical need for something called "primary key".

A relation between two tables is only allowed to be based on one field. If not, Qlik will make Syn(thetic) keys to fix this problem.

Re: Need to make Primary key

Wed, 20 Jul 2016 09:45:27 GMT

Thanks for the update. The data is for two period and it is coming in two separate rows. I need it in a single row for each unique entity e.g. entity ABC should be in one for Period 1 and Period 2. Right now it is coming as ABC Period 1 in first row and ABC Period 2 in second row.

Re: Need to make Primary key

danansell42 — Wed, 20 Jul 2016 09:49:55 GMT

Depending on your data it would probably be best to either join or concatenate 2 of the tables in order to remove the synthetic keys.

Re: Need to make Primary key

Wed, 20 Jul 2016 09:56:23 GMT

Qlikview joins the table on the basis of column name. Synthetic keys are created when two or more table have more than one common column name. It slow down the performance of application.

How to Removed Synthetic keys:

- Renaming a fileds use( As) ex: Customer as %Customer

-comment out unneccesary fields ex: // Customer,

-Concatenating fields to create single composite key.

Re: Need to make Primary key

jjbom1990 — Wed, 20 Jul 2016 09:56:49 GMT

What you can do is give each table their own table1ID, table2ID, TableNID.

you can do this by adding this to the script '1' as Table1ID or something similar.

Then you create a fact table which connects all the tables together.

more info can be found here:

Qlik Tips: Rules for creating a Key/Link Table in QlikView

and here

Concatenate vs Link Table

Re: Need to make Primary key

Anonymous — Wed, 20 Jul 2016 10:00:08 GMT

You can remove syn keys with renaming field name or if it is not required comment those fields

Hope it helps

Re: Need to make Primary key

Wed, 20 Jul 2016 10:08:24 GMT

Thanks, how do I do this.

Also, I have posted one more query, how to show records in single row for same entity if the data is for two period

Re: Need to make Primary key

tyagishaila — Wed, 20 Jul 2016 10:09:58 GMT

By which field you want to link all excel sheets, make sure that field name must be same in all load.

If you share sample data than things can be clear more.

Re: Need to make Primary key

Anonymous — Wed, 20 Jul 2016 10:12:38 GMT

Hi,

Check if the common field has the EXACT same name (no spaces etc.) in your spreadsheet. If not, rename the fields in your load script.

This will prevent QV from creating synthetic keys like '$Syn1'.

Regards.

Re: Need to make Primary key

Anonymous — Wed, 20 Jul 2016 10:14:17 GMT

To remove synthetic keys you can use qualify to prefix the fieldnames with the in memory table name (i.e. change fieldname company in table A to A.company) therefore making the fields unique in the data model. If you have a field named company in table A & B you would end up with A.company and B.company using qualify statement.

Below is a basic example using quality statements:

Qualify company;

Unqualify uniqueID;

The above would prefix the company field with the table name but leave the uniqueID field unaffected so you could use this to associate between tables.

Hope this is useful to you but you can get more info on Qualify in the forum.

Re: Need to make Primary key

Wed, 20 Jul 2016 10:20:51 GMT

Field names are the same in all files

Re: Need to make Primary key

awhitfield — Wed, 20 Jul 2016 11:49:50 GMT

Hi Suresh,

things like this are MUCH easier to help with if you SUPPLY an example QVW and sample data files!

HTH

Andy

Re: Need to make Primary key

Wed, 20 Jul 2016 12:13:40 GMT

Hi Andrew,

As suggested, have attached sample files

1) 3 xlsx file with one sample data each. Common field being 'ScripCode'

2) QVW file

3) Snap of Table viewer

Hope this help to understand the issue

Re: Need to make Primary key

Anonymous — Wed, 20 Jul 2016 12:15:40 GMT

Post sample data

Re: Need to make Primary key

Wed, 20 Jul 2016 12:16:54 GMT

Hope the attached file helps to understand the issue. The common field in xlsx is 'ScripCode'

Re: Need to make Primary key

Wed, 20 Jul 2016 12:20:35 GMT

Hi Anil,

As suggested, have attached sample files

1) 3 xlsx file with one sample data each. Common field being 'ScripCode'

2) QVW file

3) Snap of Table viewer

Hope this help to understand the issue

Re: Need to make Primary key

Wed, 20 Jul 2016 13:04:21 GMT

Hi,

concatenate BSE14 and BSE16.

Then create composite key as ScripCode&Symbol as Key in newly created table and ScpMst table.

reanme this field as table.fieldName if required or comment out if not necessary.

Re: Need to make Primary key

awhitfield — Wed, 20 Jul 2016 15:40:18 GMT

may be as simple as this:

S14:
LOAD
ScripCode,
//Symbol,
AvgQtyDec14,
[AvgT/oDec14],
[AvgT/oDec14L],
AvIn16
FROM
S14.xlsx
(ooxml, embedded labels, table is BSE14);

S16:
LOAD
ScripCode,
// Symbol,
AvgQtyFeb16,
[AvgT/oFeb16],
[AvgT/oFeb16L]
// AvIn16
FROM
S16.xlsx
(ooxml, embedded labels, table is BSE16);

SScpmst:
LOAD
ScripCode,
ISIN,
Symbol,
ScripGroup
FROM
SScpmst.xlsx
(ooxml, embedded labels, table is ScpMst);

Gives:

Andy