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
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.
I will try it!!
Thank you very much.
Hi,
I tried your method, but it always creates me $ syn.
I renamed
Then it all crashes and Qlikview bug.
What should I do?
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...
$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.