Synthetic Key Problem

[#1]

Hello,

Can you help me please:

I'am on an internship subject where I have to make of Qlik Sense the tool for the reporting of the firm's supply chain performance. And I'am facing a problem with the synthetic keys

I was not aware that the problem came from this, but after reading in the official website (Synthetic Keys Field), I understood it.

I need to load 12 fields from the database.

My Database contains 500 MB of informations and fields, that are all important without any redundancy.

I have seen in the official website of Qlik Sense, that I had to create my own non-composite key, typically using string concatenation inside an Autonumber script function.

Is this the right solution?

I don't know how to do it (I am beginner on Qlik Sense)

Can u please help guys. (It's urgent)

Thank you

[Anonymous]

Wow!! That's a lot of large synthetic keys and hard to tell what's happening from your screen grab.

Could you paste in the text of your load script, or attach it as a text file ? 

• Do make sure it does not contain anything confidential though.

[#3]

Hey, Bill

thank you for your reply,

the script contains only two parts for each table (the "LOAD" and the "SQL SELECT" parts).

no 'as'.  just the fields written and the commas at the end of it.

I'm sorry because I can't paste the script here, I'm on a Apple device, Qlik does not exist in this platform. The access is denied by USB, Network and any other way since I'm not in the office and I can only do it in the local network.

I think I can start with loading two tables first, and try to avoid the synthetic keys between them. And do so for each table I add.

All what I need is just the way to concatenate the strings as said in the official website of Qlik.

I'm sorry for my bad english.

Thank u.

[#4]

Here is the script:

SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='# ##0,00 €;-# ##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='fr-FR';
SET CreateSearchIndexOnReload=1;
SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';
SET LongMonthNames='janvier;février;mars;avril;mai;juin;juillet;août;septembre;octobre;novembre;décembre';
SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';
SET LongDayNames='lundi;mardi;mercredi;jeudi;vendredi;samedi;dimanche';

LIB CONNECT TO 'ole db';

LOAD `A/C Family`,
    CDT,
    `Champ_NEO`,
    `Commodity group`,
    `Commodity PROC`,
    `Cons MRP Controller`,
    `Cons Ordering Org`,
    `Consumer Plant`,
    Field10,
    Field12,
    Field15,
    Field17,
    Field22,
    Field24,
    Field26,
    Field29,
    Field5,
    Field7,
    `Fulfilment Rate`,
    `Indicator: Critical`,
    Location,
    Material,
    `Missing char`,
    `Nb MP`,
    `Nb MP 7 days`,
    `Nb WOL 7 days`,
    Plant,
    Program,
    `Program Family`,
    `Prov MRP Controller`,
    `Prov Ordering Org`,
    `Requirement Date`,
    `Requirement Week`,
    Station,
    `Storage Location`,
    `Supplier Type`,
    Vendor,
    `Work Order Material`,
    `Work Order Number`,
    `Work order type`,
    `Work Package`;

FROM `Req_FR`;

LOAD `A/C`,
    `A/C Family`,
    Cat,
    CDT,
    `Consuming Plant`,
    `Consuming Plant Desc`,
    `Days Missing`,
    `Entité client`,
    `Entité responsable`,
    `Fab_Element`,
    `Flux Tiré Cons`,
    `Flux Tiré Fourn`,
    `Fourn# Ordering Org#`,
    `LEGACY Prog`,
    `Ligne de produit`,
    Material,
    `Material Desc`,
    `Missing Qty`,
    `MP Date Range`,
    `MP Flag Set Date`,
    `MRP Controller client`,
    `MRP Controller client Desc`,
    `MRP Controller responsable`,
    `MRP Controller responsable Desc`,
    MSN,
    `Nb j en Mqt`,
    `Part_type`,
    `Pièce Critique`,
    Plant,
    `Product_Line`,
    Program,
    `Program Family`,
    `Qté Mqt`,
    `Required Quantity`,
    `Requirement Date`,
    `Reservation Item`,
    `Reservation No`,
    `SAP_system`,
    Semaine,
    `Site client`,
    `Station (incl stage)`,
    `Storage Location`,
    `Supplier Type`,
    `TOTAL`,
    `Type APPRO`,
    Vendor,
    `Week`,
    `Work Order Material`,
    `Work Order Material Desc`,
    `Work Order Number`,
    `Work Order type`,
    `Work Package`;

FROM `Req_MP`;

the model

what I want is to create keys (but not synthetics ones) between the tables. The alternative of using 'concatenate' before the LOAD statement transforms the model to one table. The problem is the number of tables I need to load (12 tables), I cannot use concatenate because of the memory problem (imagine all the tables concatenated into one, the execution would be very slow)

[tomovangel]

I have had the same problem last week, i had 53 syn keys, and after trying to reload my pc got blue screen error.

The way to do it is either by making your data model with a link table//

https://community.qlik.com/blogs/qlikviewdesignblog/2013/05/17/concatenate-vs-link-table

https://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/

However, I solved my problem using AS just renaming the fields, and getting the connections, only where they are needed.
Regards,
-Angel

[arvind_patil]

Hi Ahmed,

You can avoid it using Qualify Keyword also.

thanks,

Arvind Patil

[Anonymous · Accepted solution]

I would try concatenating these 2 tables together, but also do not load any fields that you do not require.

I cannot comment on the other tables having not seen their structure, but concatenating them as well could be the way to go.  The way the Qlik Engine works it performs best on a single concatenated table so your assumption that it would be very slow is probably unfounded.  The main thing to ensure is that you do not run out of RAM as swapping will rapidly degrade performance.

Although in reality a star schema is often used with a central Fact table and outer Dimension tables.

[#8]

the concatenation was the right solution, even if my model contains only one table but with hundred thousand of lines.

The most important thing was the right data finally i had.

Thank You !