7 Replies Latest reply: Aug 21, 2017 8:22 AM by Ahmed El Midaoui RSS

    Synthetic Key Problem

    Ahmed El Midaoui

      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

        • Re: Synthetic Key Problem
          Bill Markham

          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.
          • Re: Synthetic Key Problem
            Ahmed El Midaoui

            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) Capture.JPG

              • Re: Synthetic Key Problem
                Bill Markham

                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.

              • Re: Synthetic Key Problem
                Arvind Patil

                Hi Ahmed,

                 

                You can avoid it using Qualify Keyword also.

                 

                thanks,

                Arvind Patil