Error Field not found - <FIELDNAME> - Page 2 - Qlik Community

Report Inappropriate Content · ‎2014-11-11

hello,
If anyone can help me please !!
I have a problem while loading my tables on Qlikview.
I found a mistake Champ - <DEV_LIB>. Yet the field is in my and my select LOAD. I can not find the error. Could you tell me what should I do?
I could not make the joins between tables. I have 9 tables. how can i do?

Here is my code:

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 MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';

SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

/* *************** Activation des options globales au document *************** */

/* Connexion OLEDB SQL Server 2014 */

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ICP;Data Source=***************;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=********************;Use Encryption for Data=False;Tag with column collation when possible=False];

/*******Variables ******/

set vSaveQVD = ;

DOCUMENTS:

LOAD "DOC_DATE",

"DEV_CODE",

"DIV_CODE",

"DOC_DT_PRV",

"DOC_EN_TTC",

"DOC_ETAT",

"DOC_NUMERO",

"DOC_PIECE",

"DOC_STYPE",

"DOC_TX_DEV",

"DOC_TXRFAC",

"DOC_TYPE",

"NAT_CODE",

"PAY_CODE",

"PCF_CODE",

"PCF_PAYEUR",

"PCF_REMMIN",

"PCF_REMVAL",

"PRJ_CODE",

"REG_CODE",

"REP_CODE",

"SAL_CODE",

"SRV_CODE",

"TAR_CODE";

SQL SELECT DOC_DATE,

DEV_CODE,

DIV_CODE,

DOC_DT_PRV,

DOC_EN_TTC,

DOC_ETAT,

DOC_NUMERO,

DOC_PIECE,

DOC_STYPE,

DOC_TX_DEV,

DOC_TXRFAC,

DOC_TYPE,

NAT_CODE,

PAY_CODE,

PCF_CODE,

PCF_PAYEUR,

PCF_REMMIN,

PCF_REMVAL,

PRJ_CODE,

REG_CODE,

REP_CODE,

SAL_CODE,

SRV_CODE,

TAR_CODE

FROM ICP.dbo.DOCUMENTS;

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

DROP TABLE DOCUMENTS;

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",

"DIV_CODE",

"DOC_DT_PRV",

"DOC_EN_TTC",

"DOC_ETAT",

"DOC_NUMERO",

"DOC_PIECE",

"DOC_STYPE",

"DOC_TX_DEV",

"DOC_TXRFAC",

"DOC_TYPE",

"NAT_CODE",

"PAY_CODE",

"PCF_CODE",

"PCF_PAYEUR",

"PCF_REMMIN",

"PCF_REMVAL",

"PRJ_CODE",

"REG_CODE",

"REP_CODE",

"SAL_CODE",

"SRV_CODE",

"TAR_CODE"

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

QUALIFY *;

UNQUALIFY "DOC_NUMERO",

"DEV_CODE",

"DIV_CODE",

"PAY_CODE",

"PCF_CODE",

"REG_CODE",

"REP_CODE",

"SAL_CODE",

"SRV_CODE",

"TAR_CODE";

SQL SELECT "DEV_CODE",

"DOC_DATE",

"DIV_CODE",

"DOC_DT_PRV",

"DOC_EN_TTC",

"DOC_ETAT",

"DOC_NUMERO",

"DOC_PIECE",

"DOC_STYPE",

"DOC_TX_DEV",

"DOC_TXRFAC",

"DOC_TYPE",

"NAT_CODE",

"PAY_CODE",

"PCF_CODE",

"PCF_PAYEUR",

"PCF_REMMIN",

"PCF_REMVAL",

"PRJ_CODE",

"REG_CODE",

"REP_CODE",

"SAL_CODE",

"SRV_CODE",

"TAR_CODE"

From DOCUMENTS;

/*WHERE DOC_TYPE IN ('A','V')*/

DEVISES:

LOAD "DEV_CODE",

"DEV_LIB",

"DEV_MONEY",

"DEV_MONEYS",

"DEV_FORMAT",

"DEV_NB_DEC",

"DEV_SYMBOL",

"DEV_DT_ACT",

"DEV_INCERT",

"DEV_COURS",

"DEV_DTEURO",

"DEV_EURO",

"DEV_DORT",

"DEV_DTMAJ",

"DEV_USRMAJ",

"DEV_NUMMAJ";

SQL SELECT DEV_CODE,

DEV_LIB,

DEV_MONEY,

DEV_MONEYS,

DEV_FORMAT,

DEV_NB_DEC,

DEV_SYMBOL,

DEV_DT_ACT,

DEV_INCERT,

DEV_COURS,

DEV_DTEURO,

DEV_EURO,

DEV_DORT,

DEV_DTMAJ,

DEV_USRMAJ,

DEV_NUMMAJ

FROM ICP.dbo.DEVISES;

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

DROP TABLE DEVISES;

DEVISES:

LOAD DEV_CODE,

DEV_LIB,

DEV_MONEY,

DEV_MONEYS,

DEV_FORMAT,

DEV_NB_DEC,

DEV_SYMBOL,

DEV_DT_ACT,

DEV_INCERT,

DEV_COURS,

DEV_DTEURO,

DEV_EURO,

DEV_DORT,

DEV_DTMAJ,

DEV_USRMAJ,

DEV_NUMMAJ

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

QUALIFY *;

UNQUALIFY "DEV_CODE";

SQL SELECT "DEV_CODE",

"DEV_LIB",

"DEV_MONEY",

"DEV_MONEYS",

"DEV_FORMAT",

"DEV_NB_DEC",

"DEV_SYMBOL",

"DEV_DT_ACT",

"DEV_INCERT",

"DEV_COURS",

"DEV_DTEURO",

"DEV_EURO",

"DEV_DORT",

"DEV_DTMAJ",

"DEV_USRMAJ",

"DEV_NUMMAJ"

From DEVISES;

LIGNES:

LOAD "DOC_NUMERO",

"LIG_NUMERO",

"LIG_SUBNUM",

"LIG_QTE",

"REP_CODE",

"ART_CODE",

"ART_NCOLIS",

"ART_TGAMME",

"DEP_CODE",

"PRJ_CODE",

"SAL_CODE",

"TAR_CODE",

"LIG_P_BRUT",

"LIG_TOTAL",

"NAT_TVATX",

"LIG_COUT";

SQL SELECT "DOC_NUMERO",

"LIG_NUMERO",

"LIG_SUBNUM",

"LIG_QTE",

"REP_CODE",

"ART_CODE",

"ART_NCOLIS",

"ART_TGAMME",

"DEP_CODE",

"PRJ_CODE",

"SAL_CODE",

"TAR_CODE",

"LIG_P_BRUT",

"LIG_TOTAL",

"NAT_TVATX",

"LIG_COUT"

FROM ICP.dbo.LIGNES;

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

DROP TABLE LIGNES;

LIGNES:

LOAD AUTONUMBERHASH128(DOC_NUMERO& '|' &LIG_NUMERO& '|' &LIG_SUBNUM) AS ID_LIGNES,

"DOC_NUMERO",

"LIG_NUMERO",

"LIG_SUBNUM",

"LIG_QTE",

"REP_CODE",

"ART_CODE",

"ART_NCOLIS",

"ART_TGAMME",

"DEP_CODE",

"PRJ_CODE",

"SAL_CODE",

"TAR_CODE",

"LIG_P_BRUT",

"LIG_TOTAL",

"NAT_TVATX",

"LIG_COUT",

"LIG_UB" as CODEL

from $(vSaveQVD) LIGNES.QVD(qvd);

QUALIFY *;

UNQUALIFY "DOC_NUMERO",

"LIG_NUMERO",

"LIG_SUBNUM",

"DEP_CODE",

"PRJ_CODE",

"SAL_CODE",

"TAR_CODE",

"CODEL";

SQL SELECT "DOC_NUMERO",

"LIG_NUMERO",

"LIG_SUBNUM",

"LIG_QTE",

"REP_CODE",

"ART_CODE",

"ART_NCOLIS",

"ART_TGAMME",

"DEP_CODE",

"PRJ_CODE",

"SAL_CODE",

"TAR_CODE",

"LIG_P_BRUT",

"LIG_TOTAL",

"NAT_TVATX",

"LIG_COUT",

"LIG_UB" as CODEL

From LIGNES

WHERE LIG_TYPE NOT IN ('x', 'X', 'T');

Thank you

rubenmarin · ‎2014-11-12

2 options:

- keep the qualify/unqualify approach

- renaming fields:No use of qualify/unqualify. The fields linking tables should have the same name, the others should have an unique name.

I think renaming fields is a best practice.

I usually have one document that creates the qvds with the original table names. No alias here.

Then I create the document for the user, reading from qvds. In this document is where I rename the fields.

In your document you only need to rename fields when you are reading from qvds:

LOAD ....

from $(vSaveQVD) FILENAME.QVD(qvd)

Hope I had explained.

Report Inappropriate Content · ‎2014-11-12

I will try it!!

Thank you very much.

Report Inappropriate Content · ‎2014-11-12

Hi,

I tried your method, but it always creates me $ syn.

I renamed

Then it all crashes and Qlikview bug.

What should I do?

Report Inappropriate Content · ‎2014-11-12

Hi,

Concatenate the Documents with the Inline Table

like

LOAD INLINE *

[ TestField

];

Concatenate

Documents:

-------------

--------------

concatenate

SQL SELECT DOC_DATE,

DEV_CODE,

DIV_CODE,

DOC_DT_PRV,

DOC_EN_TTC,

DOC_ETAT,

DOC_NUMERO,

DOC_PIECE,

DOC_STYPE,

DOC_TX_DEV,

DOC_TXRFAC,

DOC_TYPE,

NAT_CODE,

PAY_CODE,

PCF_CODE,

PCF_PAYEUR,

PCF_REMMIN,

PCF_REMVAL,

PRJ_CODE,

REG_CODE,

REP_CODE,

SAL_CODE,

SRV_CODE,

TAR_CODE

FROM ICP.dbo.DOCUMENTS;

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

DROP TABLE DOCUMENTS;

This will help you...

rubenmarin · ‎2014-11-13

$Syn are created when there is more than one field with the same name in 2 tables, to avoid those you need to create a unique key field, usually this is done concatenating the different key fields,ie:

In Documents you can have:

LOAD DOC_NUMERO & '#' & DEV_CODE as IdDoc,

DOC_NUMERO

...

and in devices:

LOAD DOC_NUMERO & '#' & DEV_CODE as IdDoc,

DEV_CODE,

...

Note that DOC_NUMERO is only loaded in Documents and DEV_CODE only in Devices, IdDoc is the only field loaded in both tables.