1 Reply Latest reply: Nov 11, 2014 11:46 AM by Ruben Marin RSS

    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 = [C:\Users\vincentl\Desktop\Projet QlikView\QVDs\];

       

      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