Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi, i suspect it's git something to do with the qualified field,
try comment your loads, and uncomment one at a time to try and pinpoint which load is giving the error.
regards,
JP
I think Jonathan is right, most probably is the one before you load DEVICES to crate QVD, set unqualify * before creating the load that creates the qvd
that was just a thought yeah,
we've seen a lot of questions concerning load error similar to yours, where the issue was a matter of case sensitivity, renamed fields, or even dropped fields.
something, somewhere has messed up, so it takes a bit of digging,isolating codes.. etc.
Hi,
Jonathan is right do reload your application step by step by commenting rest of the load and by this you found the error in the load script.
Regards
Anand
The Qualify statement renames your field names to tablename.fieldname, e.g. DEV_LIB becomes DEVISES.DEV_LIB. So anywhere where you're not loading directly from source (preceding, resident, qvd loads) you need to refer to DEVISES.DEV_LIB instead of DEV.LIB
Hi,
Thank you to all for your help.
I have put (Qualify) in comment. The problem was (Qualify * ;),
but how to avoid a loading too heavy? Qlikview creates $ syn, it creates bugs and slow loading
.
Regards
Hi,
To keep it easy, remove the comment in "Qualify *" and set "unqualify *", before the load that creates DEVICES.QVD:
...
From DOCUMENTS;
/*WHERE DOC_TYPE IN ('A','V')*/
unqualify *;
DEVISES:
LOAD "DEV_CODE",
...
Also, you probably need to set the
QUALIFY *;
UNQUALIFY "DEV_CODE";
before the load that reads from DEVICES.QVD
Usually, you should use alias (as) sentence to rename field and avoid the use qualify or unqualify, using it only if there is really neccesary, so for each field you create a unique name, ie:
LOAD "DOC_PIECE" as [Document Piece]
...
For those fields you want to use to join tables you set the same name in the tables you want to join, ie:
LOAD DEV_CODE as IdDEV
And for tables with more than one key you can create a concatenated key:
LOAD DOC_NUMERO & '#' & DEV_CODE as IdDoc
There are more tricks to keep a good data model, but all this comes with experience.
Anyway, I make a little changes in your script keeping the qualify/unqualify approach, i can't test it so maybe there is an error.
Hope this helps!
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;
QUALIFY *;
UNQUALIFY "DOC_NUMERO",
"DEV_CODE",
"DIV_CODE",
"PAY_CODE",
"PCF_CODE",
"REG_CODE",
"REP_CODE",
"SAL_CODE",
"SRV_CODE",
"TAR_CODE";
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);
UNQUALIFY *;
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;
QUALIFY *;
UNQUALIFY "DEV_CODE";
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);
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;
QUALIFY *;
UNQUALIFY "DOC_NUMERO",
"LIG_NUMERO",
"LIG_SUBNUM",
"DEP_CODE",
"PRJ_CODE",
"SAL_CODE",
"TAR_CODE",
"CODEL";
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) WHERE LIG_TYPE NOT IN ('x', 'X', 'T');
Hi Ruben,
Thank you for your help.
I'm sorry, I do not understand your answer well!!.
I shoulds rename fields (primary key), and remove Qualify, unqualify? is that right?
Could you please be more clear.
Thank you.
regards