7 Replies Latest reply: Nov 13, 2014 10:18 AM by SOULEIMANE BELARIBI RSS

    Syn Keys avoid please help

      Hi Guys

       

      I am loading from 3 excel sheets and I am getting syn keys, see below,

       

      I have tried to use a link table provided by Lars Christensen but I am getting all sorts of errors.

       

      Can anyone please help me avoid these keys ?

      Below is the bare script , I have tried a few different work arounds but just not geting it.

       

      Thanks

       

      A

       

      LOAD

      BRANCH_CODE,

      [*BUSINESS UNIT],

      CUST_NUMBER,

      FULL_NAME,

      ACTIVE_STATUS,

      CURRENCY,

      MAKER_ID,

      MAKER_DT_STAMP,

      CHECKER_ID,

      CHECKER_DT_STAMP,

      [CYCLOPS NO],

      [*COINS ID],

      [PARENT BRANCH R4],

      [*CB SECT. CODE],

      [*CB SECTOR CODE],

      [*INDUSTRY CODE],

      [BORROWER GRADE-BBI],

      [BORROWER GRADE-CGB],

      'Customer' as Action

      FROM

      [S:\Customer Static as at D051211.xls]

      (biff, embedded labels, table is [SQL Results$]);

       

      LOAD

      LIAB_BR,

      [*BUSINESS UNIT LIMIT],

      LIAB_ID,

      FULL_NAME,

      LINE_ID,

      MOD_NO,

      RECORD_STAT,

      MAKER_ID,

      MAKER_DT_STAMP,

      CHECKER_ID,

      CHECKER_DT_STAMP,

      [*LENDING TYPE],

      [AGENT BANK DETAILS-FREE TEXT],

      [*CREDIT COMMITTEE EXPIRY DATE],

      [*ACA FACILITY CODE],

      [*SONIC FACILITY ID],

      LINE_CURRENCY,

      REVOLVING_LINE,

      LINE_START_DATE,

      LINE_EXPIRY_DATE,

      EXPECTED_MATURITY_DATE,

      ACTIVE_STATUS,

      LIMIT_AMOUNT,

      AVAILABLE_AMOUNT,

      TERM_LIMIT,

      UTILISATION,

      'Limit' as Action

      FROM

      [S:\Limit Static as at D051211.xls]

      (biff, embedded labels, table is [SQL Results$]);

       

      LOAD

      BRANCH_CODE,

      [*BUSINESS UNIT],

      CURRENCY,

      CUST_NUMBER,

      FULL_NAME,

      CONTRACT_REF_NO,

      PRODUCT_TYPE,

      MAKER_ID,

      MAKER_DT_STAMP,

      CHECKER_ID,

      CHECKER_DT_STAMP,

      EVENT_CODE,

      DUE_DATE,

      PAID_DATE,

      UNSTRUCTURED_LOAN,

      'OOC' as Action

      FROM

      [S:\Out Of Course Principal Reductions as at D051211.xls]

      (biff, embedded labels, table is [SQL Results$]);

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      $Syn 1 = FULL_NAME+MAKER_ID+MAKER_DT_STAMP+CHECKER_ID+CHECKER_DT_STAMP

      $Syn 2 = FULL_NAME+ACTIVE_STATUS+MAKER_ID+MAKER_DT_STAMP+CHECKER_ID+CHECKER_DT_STAMP

      $Syn 3 = BRANCH_CODE+*BUSINESS UNIT+CUST_NUMBER+FULL_NAME+CURRENCY+MAKER_ID+MAKER_DT_STAMP+CHECKER_ID+CHECKER_DT_STAMP

      $Syn 4 = $Syn 1+$Syn 3

      $Syn 5 = $Syn 1+$Syn 2

      $Syn 6 = $Syn 1+$Syn 2+$Syn 3

      $Syn 7 = $Syn 4+$Syn 5+$Syn 6

        • Syn Keys avoid please help
          Karl Pover

          How should each table be linked to the other?  You could try concatenating the 3 tables.

           

          Table_1:

          Load *

          From ...;

           

          concatentate (Table_1)

          Table_2:

          Load *

          From ...;

           

          ...

           

           

          Regards, Karl

          • Syn Keys avoid please help
            Kamal Naithani

            Hi friend you are getting the synthetic key because there are more than one field common in your table.Use the folowing methods

             

            1.You can use join or concatenation

            Table1:

            load all the field

            Concatenate

            Table2

            All field

            2.Use proper aliasing if needed.for example if Emp.no is making a synthetic key between two table than.replace

            Emp.no as Employee no in one table

            .

            Hope this will help you.

             

             

            regards

            Kamal

            • Syn Keys avoid please help
              Arun Prasadh Narasimhalu

              Hi Friend,

               

              Rename( Like putting aliases) the fields which is common in all the tables or

              use qualify command bfore loading the next two tables, if you use qualify command then it ll automatically rename all the fields or

              use composite key technic to avoid synthetic key formation or loop formation.

               

              Try it...

               

              Thanks & Regards,

              Arun Prasadh.N.

              • Re: Syn Keys avoid please help
                Arun Prasadh Narasimhalu

                Hi Friend,

                 

                Rename( Like putting aliases) the fields which is common in all the tables or

                use qualify command bfore loading the next two tables, if you use qualify command then it ll automatically rename all the fields or

                use composite key technic to avoid synthetic key formation or loop formation.

                 

                Try it...

                 

                Thanks & Regards,

                Arun Prasadh.N.

                  • Syn Keys avoid please help

                    Thanks Guys

                     

                    Ive used a combination of both

                     

                    Kind regards

                     

                    Anne

                      • Re: Syn Keys avoid please help

                        hi,

                         

                        I have the same problem!

                        I would to aviod $Syn, I have used Qualify, unqualify, but it does not work.

                        how were you done? would you like give me an example please?

                         

                        this is my script:

                        DOCUMENTS:

                        LOAD     "DOC_DATE",

                                "DEV_CODE",

                                "DOC_DT_PRV",

                                "DOC_EN_TTC",

                                "DOC_ETAT",

                                "DOC_NUMERO",

                                "DOC_PIECE",

                                "DOC_STYPE",

                                "DOC_TX_DEV",

                                "DOC_TX_ESC",

                                "DOC_TXRFAC",

                                "PCF_REMVAL",

                                "DOC_TYPE",

                                "PAY_CODE",

                                "PCF_CODE";

                         

                        SQL SELECT DOC_DATE,

                                    DEV_CODE,

                                    DOC_DT_PRV,

                                    DOC_EN_TTC,

                                    DOC_ETAT,

                                    DOC_NUMERO,

                                    DOC_PIECE,

                                    DOC_STYPE,

                                    DOC_TX_DEV,

                                    DOC_TX_ESC,

                                    DOC_TXRFAC,

                                    DOC_TYPE,

                                    PAY_CODE,

                                    PCF_REMVAL,

                                    PCF_CODE

                         

                        FROM ICP.dbo.DOCUMENTS;

                         

                        STORE DOCUMENTS into $(vSaveQVD) DOCUMENTS.QVD(qvd);

                         

                        DROP TABLE DOCUMENTS;

                        QUALIFY *;

                        DOCUMENTS:

                        LOAD     "DOC_DATE",

                                Month(DOC_DATE) as [Mois],

                                Year("DOC_DATE") as [Année],

                                Date("DOC_DATE") as [Date],

                                If(DOC_TYPE = 'A', 'Achat', if(DOC_TYPE = 'V', 'Ventes')) as [Type de doc],

                                If(DOC_TYPE = 'V',

                                  if(DOC_STYPE = 'P', 'Pro-forma',

                                  if(DOC_STYPE = 'D', 'Devis',

                                  if(DOC_STYPE = 'C', 'Commandes',

                                  if(DOC_STYPE = 'B', 'Bons de Livraison',

                                  if(DOC_STYPE = 'R', 'Bons de Retour',

                                  if(DOC_STYPE = 'F', 'Factures',

                                  if(DOC_STYPE = '1', 'Factures Financières',

                                  if(DOC_STYPE = 'A', 'Avoirs',

                                  if(DOC_STYPE = '0', 'Avoirs Financiers',

                                  ))))))))),

                                  if(DOC_TYPE =  'A',

                                  if(DOC_STYPE = 'D', 'Demandes de Prix',

                                  if(DOC_STYPE = 'C', 'Commandes',

                                  if(DOC_STYPE = 'B', 'Bons de Réceptions',

                                  if(DOC_STYPE = 'R', 'Bons de Retour',

                                  if(DOC_STYPE = 'F', 'Factures',

                                  if(DOC_STYPE = '1', 'Factures Financières',

                                  if(DOC_STYPE = 'A', 'Avoirs',

                                  if(DOC_STYPE = '0', 'Avoirs Financiers')

                                  ))))))))

                                  ) as [Sous_Type_de_doc],

                                "DEV_CODE" AS IdDEV,

                                "DOC_DT_PRV",

                                "DOC_EN_TTC",

                                "DOC_ETAT",

                                "DOC_NUMERO" as IdDoc,

                                "DOC_PIECE" AS 'N° Pièce',

                                "DOC_STYPE",

                                "DOC_TX_DEV" AS 'Cours Devise',

                                "DOC_TX_ESC",

                                "DOC_TXRFAC",

                                "DOC_TYPE",

                                "PAY_CODE" as IdPay,

                                "PCF_REMVAL",

                                "PCF_CODE" as idTiers

                        FROM  $(vSaveQVD) DOCUMENTS.QVD(qvd);

                        UNQUALIFY IdDoc,IdDEV,IdPay,idTiers;

                        SQL SELECT  "DEV_CODE",

                                    "DOC_DATE",

                                    "DOC_DT_PRV",

                                    "DOC_EN_TTC",

                                    "DOC_ETAT",

                                    "DOC_NUMERO",

                                    "DOC_PIECE",

                                    "DOC_STYPE",

                                    "DOC_TX_DEV",

                                    "DOC_TX_ESC",

                                    "DOC_TXRFAC",

                                    "DOC_TYPE",

                                    "PAY_CODE",

                                    "PCF_REMVAL",           

                                    "PCF_CODE"

                        From DOCUMENTS

                        WHERE DOC_TYPE IN ('A','V');

                         

                        DEVISES:

                        LOAD     "DEV_CODE",

                                "DEV_LIB",

                                "DEV_INCERT";

                               

                        SQL SELECT  DEV_CODE,

                                    DEV_LIB,

                                    DEV_INCERT

                        FROM ICP.dbo.DEVISES;

                         

                        STORE DEVISES into $(vSaveQVD) DEVISES.QVD(qvd);

                         

                        DROP TABLE DEVISES;

                         

                        QUALIFY *;

                        DEVISES:

                        LOAD    DEV_CODE as IdDEV,

                                DEV_CODE as IdDEV_ARC,

                                DEV_LIB AS 'Devise',

                                DEV_INCERT

                               

                        FROM $(vSaveQVD) DEVISES.QVD(qvd);

                        UNQUALIFY IdDEV,IdDEV_ARC;

                        SQL SELECT "DEV_CODE",

                                "DEV_LIB",

                                "DEV_INCERT"

                        From DEVISES;

                         

                         

                        Regards

                    • Re: Syn Keys avoid please help

                      hi,

                       

                      I have the same problem!

                      I would to aviod $Syn, I have used Qualify, unqualify, but it does not work.

                      how were you done? would you like give me an example please?

                       

                      this is my script:

                      DOCUMENTS:

                      LOAD     "DOC_DATE",

                              "DEV_CODE",

                              "DOC_DT_PRV",

                              "DOC_EN_TTC",

                              "DOC_ETAT",

                              "DOC_NUMERO",

                              "DOC_PIECE",

                              "DOC_STYPE",

                              "DOC_TX_DEV",

                              "DOC_TX_ESC",

                              "DOC_TXRFAC",

                              "PCF_REMVAL",

                              "DOC_TYPE",

                              "PAY_CODE",

                              "PCF_CODE";

                       

                      SQL SELECT DOC_DATE,

                                  DEV_CODE,

                                  DOC_DT_PRV,

                                  DOC_EN_TTC,

                                  DOC_ETAT,

                                  DOC_NUMERO,

                                  DOC_PIECE,

                                  DOC_STYPE,

                                  DOC_TX_DEV,

                                  DOC_TX_ESC,

                                  DOC_TXRFAC,

                                  DOC_TYPE,

                                  PAY_CODE,

                                  PCF_REMVAL,

                                  PCF_CODE

                       

                      FROM ICP.dbo.DOCUMENTS;

                       

                      STORE DOCUMENTS into $(vSaveQVD) DOCUMENTS.QVD(qvd);

                       

                      DROP TABLE DOCUMENTS;

                      QUALIFY *;

                      DOCUMENTS:

                      LOAD     "DOC_DATE",

                              Month(DOC_DATE) as [Mois],

                              Year("DOC_DATE") as [Année],

                              Date("DOC_DATE") as [Date],

                              If(DOC_TYPE = 'A', 'Achat', if(DOC_TYPE = 'V', 'Ventes')) as [Type de doc],

                              If(DOC_TYPE = 'V',

                                if(DOC_STYPE = 'P', 'Pro-forma',

                                if(DOC_STYPE = 'D', 'Devis',

                                if(DOC_STYPE = 'C', 'Commandes',

                                if(DOC_STYPE = 'B', 'Bons de Livraison',

                                if(DOC_STYPE = 'R', 'Bons de Retour',

                                if(DOC_STYPE = 'F', 'Factures',

                                if(DOC_STYPE = '1', 'Factures Financières',

                                if(DOC_STYPE = 'A', 'Avoirs',

                                if(DOC_STYPE = '0', 'Avoirs Financiers',

                                ))))))))),

                                if(DOC_TYPE =  'A',

                                if(DOC_STYPE = 'D', 'Demandes de Prix',

                                if(DOC_STYPE = 'C', 'Commandes',

                                if(DOC_STYPE = 'B', 'Bons de Réceptions',

                                if(DOC_STYPE = 'R', 'Bons de Retour',

                                if(DOC_STYPE = 'F', 'Factures',

                                if(DOC_STYPE = '1', 'Factures Financières',

                                if(DOC_STYPE = 'A', 'Avoirs',

                                if(DOC_STYPE = '0', 'Avoirs Financiers')

                                ))))))))

                                ) as [Sous_Type_de_doc],

                              "DEV_CODE" AS IdDEV,

                              "DOC_DT_PRV",

                              "DOC_EN_TTC",

                              "DOC_ETAT",

                              "DOC_NUMERO" as IdDoc,

                              "DOC_PIECE" AS 'N° Pièce',

                              "DOC_STYPE",

                              "DOC_TX_DEV" AS 'Cours Devise',

                              "DOC_TX_ESC",

                              "DOC_TXRFAC",

                              "DOC_TYPE",

                              "PAY_CODE" as IdPay,

                              "PCF_REMVAL",

                              "PCF_CODE" as idTiers

                      FROM  $(vSaveQVD) DOCUMENTS.QVD(qvd);

                      UNQUALIFY IdDoc,IdDEV,IdPay,idTiers;

                      SQL SELECT  "DEV_CODE",

                                  "DOC_DATE",

                                  "DOC_DT_PRV",

                                  "DOC_EN_TTC",

                                  "DOC_ETAT",

                                  "DOC_NUMERO",

                                  "DOC_PIECE",

                                  "DOC_STYPE",

                                  "DOC_TX_DEV",

                                  "DOC_TX_ESC",

                                  "DOC_TXRFAC",

                                  "DOC_TYPE",

                                  "PAY_CODE",

                                  "PCF_REMVAL",           

                                  "PCF_CODE"

                      From DOCUMENTS

                      WHERE DOC_TYPE IN ('A','V');

                       

                      DEVISES:

                      LOAD     "DEV_CODE",

                              "DEV_LIB",

                              "DEV_INCERT";

                             

                      SQL SELECT  DEV_CODE,

                                  DEV_LIB,

                                  DEV_INCERT

                      FROM ICP.dbo.DEVISES;

                       

                      STORE DEVISES into $(vSaveQVD) DEVISES.QVD(qvd);

                       

                      DROP TABLE DEVISES;

                       

                      QUALIFY *;

                      DEVISES:

                      LOAD    DEV_CODE as IdDEV,

                              DEV_CODE as IdDEV_ARC,

                              DEV_LIB AS 'Devise',

                              DEV_INCERT

                             

                      FROM $(vSaveQVD) DEVISES.QVD(qvd);

                      UNQUALIFY IdDEV,IdDEV_ARC;

                      SQL SELECT "DEV_CODE",

                              "DEV_LIB",

                              "DEV_INCERT"

                      From DEVISES;

                       

                      regards