topic Re: Syn Keys avoid please help in QlikView

Syn Keys avoid please help

Wed, 21 Dec 2011 15:07:31 GMT

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

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

Syn Keys avoid please help

pover — Wed, 21 Dec 2011 22:53:10 GMT

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

kamalqlik — Thu, 22 Dec 2011 04:47:56 GMT

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

Thu, 22 Dec 2011 05:03:28 GMT

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

Thu, 22 Dec 2011 05:04:56 GMT

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

Thu, 22 Dec 2011 09:25:44 GMT

Thanks Guys

Ive used a combination of both

Kind regards

Anne

Re: Syn Keys avoid please help

Thu, 13 Nov 2014 15:17:15 GMT

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

Thu, 13 Nov 2014 15:17:58 GMT

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