Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Field not found - <FIELDNAME> ERROR. HELP

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

1 Reply

Re: Field not found - <FIELDNAME> ERROR. HELP

Hi slim,

You can check the answers you get on Error Field not found - &lt;FIELDNAME&gt; 

Community Browser