Syn Keys avoid please help

[#1]

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

(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

(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

(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

[pover · Accepted solution]

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

[kamalqlik]

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

[#4]

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.

[#5]

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.

[#6]

Thanks Guys

Ive used a combination of both

Kind regards

Anne

[#7]

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

[#8]

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