topic Re: INNER JOIN Issue Multiple Table in QlikView

INNER JOIN Issue Multiple Table

theboss-123 — Mon, 20 Sep 2021 20:00:57 GMT

Hello ,

When i try to load data from Database through inner join i got the error

Table not found error

Table 'Article' not found

however there is common fields, and please should be informed that the default join of qlikview allow to load data without error

here is my script

Docligne :

load
DO_Piece as ID_Dim3,
DO_Type as ID_Dim4,
AR_Ref as ID_DIM1,
AR_Ref & '_' & DL_Design as Article,
DL_Design as libelé,
DL_PieceBC,
DO_Date,
Year ("DO_Date") as annes,
DL_PrixUnitaire as PU,
DL_MontantHT ,
DL_MontantTTC,
DL_PUDevise,
DL_Qte,
CT_Num as N°_CLIENT,
DL_No as ID_Dim7,
DE_No as N°_DEPOT ,

Date(DO_Date) As Date,
Year(DO_Date) As Year,
Month(DO_Date) As Month,
Day(DO_Date) As Day,
Week(DO_Date) As Week,
'Q' & Ceil(Month(DO_Date)/3) As Quarter,
WeekYear(DO_Date) & Num(Week(DO_Date), '00') As YearWeek,
Year(DO_Date) & '-Q' & Ceil(Month(DO_Date)/3) As YearQuarter,
Date(MonthStart(DO_Date), 'YYYY-MM') As YearMonth,
-Year2Date(DO_Date) As YTD_Flag,
-Year2Date(DO_Date, -1) As LYTD_Flag;

SQL SELECT *
FROM "LM".dbo."F_DOCLIGNE";

inner join (Docligne)

Article :
load
AR_Ref as ID_DIM1,
AR_Design as Designation,
FA_CodeFamille as ID_DIM2,
AR_SuiviStock as LOT_TYPE;

SQL SELECT *
FROM "LM".dbo."F_ARTICLE";

inner join (Article)

Famille :

Load
FA_CodeFamille as ID_DIM2,
FA_Intitule as Famille;

SQL SELECT "FA_CodeFamille",
"FA_Intitule"
FROM "LM".dbo."F_FAMILLE";

inner join (Docligne)

LOT :
load
LS_NoSerie as N°_LOT,
DL_NoOut as ID_Dim7,
LS_Qte ,
LS_MvtStock;

SQL SELECT *
FROM LM.dbo."F_LOTSERIE";

inner join (Docligne)

DEPOT:
load

DE_No as N°_DEPOT ,
DE_Intitule as DEPOT;
SQL SELECT *
FROM "LM".dbo."F_DEPOT";

inner join (Docligne)

DOCENTETE:
load
DO_Piece as ID_Dim3,
DO_Type as ID_Dim4,
DO_Tiers as ID_Dim5,
LI_No as ID_Dim6,
DO_Ref;
SQL SELECT *
FROM "LM".dbo."F_DOCENTETE";

inner join (DOCENTETE)

Livraison :
Load

LI_No as ID_Dim6,
LI_Intitule as Client_Livré,
LI_Pays as Pays_Livré;
SQL SELECT *
FROM "LM".dbo."F_LIVRAISON";
inner join (DOCENTETE)

PARTNER:
load
CT_Num as ID_Dim5,

CT_Intitule as PARTNER;

SQL SELECT *
FROM "LM".dbo."F_COMPTET";

inner join(Docligne)

Transaction:
LOAD ID_Dim4,
Descreption
FROM
[D:\01_Works & mails & ressources\59_UNIMER DAKHLA\analyse qlikview\Transaction.xlsx]
(ooxml, embedded labels, table is Feuil1);

Re: INNER JOIN Issue Multiple Table

abhijitnalekar — Mon, 20 Sep 2021 20:13:31 GMT

You should use table name Docligne instead Article.

Reason being when you join first two tables Article table joins with Docligne table article table gets dissolve in first table...

Re: INNER JOIN Issue Multiple Table

theboss-123 — Mon, 20 Sep 2021 20:17:52 GMT

i would like to thank you first for your reply can you rewrite me the script with the right code ?

Re: INNER JOIN Issue Multiple Table

abhijitnalekar — Mon, 20 Sep 2021 20:32:28 GMT

Hi,

Please check the below script.

Docligne :

SQL SELECT *
FROM "LM".dbo."F_DOCLIGNE";

inner join (Docligne)

Article :
load
AR_Ref as ID_DIM1,
AR_Design as Designation,
FA_CodeFamille as ID_DIM2,
AR_SuiviStock as LOT_TYPE;

SQL SELECT *
FROM "LM".dbo."F_ARTICLE";

inner join (Docligne)

Famille :

Load
FA_CodeFamille as ID_DIM2,
FA_Intitule as Famille;

SQL SELECT "FA_CodeFamille",
"FA_Intitule"
FROM "LM".dbo."F_FAMILLE";

inner join (Docligne)

LOT :
load
LS_NoSerie as N°_LOT,
DL_NoOut as ID_Dim7,
LS_Qte ,
LS_MvtStock;

SQL SELECT *
FROM LM.dbo."F_LOTSERIE";

inner join (Docligne)

DEPOT:
load

DE_No as N°_DEPOT ,
DE_Intitule as DEPOT;
SQL SELECT *
FROM "LM".dbo."F_DEPOT";

inner join (Docligne)

DOCENTETE:
load
DO_Piece as ID_Dim3,
DO_Type as ID_Dim4,
DO_Tiers as ID_Dim5,
LI_No as ID_Dim6,
DO_Ref;
SQL SELECT *
FROM "LM".dbo."F_DOCENTETE";

inner join (Docligne)

Livraison :
Load

LI_No as ID_Dim6,
LI_Intitule as Client_Livré,
LI_Pays as Pays_Livré;
SQL SELECT *
FROM "LM".dbo."F_LIVRAISON";
inner join (Docligne)

PARTNER:
load
CT_Num as ID_Dim5,

CT_Intitule as PARTNER;

SQL SELECT *
FROM "LM".dbo."F_COMPTET";

inner join(Docligne)

Transaction:
LOAD ID_Dim4,
Descreption
FROM
[D:\01_Works & mails & ressources\59_UNIMER DAKHLA\analyse qlikview\Transaction.xlsx]
(ooxml, embedded labels, table is Feuil1);

Re: INNER JOIN Issue Multiple Table

theboss-123 — Mon, 20 Sep 2021 21:20:24 GMT

Thanks but in my case i have two fact table Docligne and Docentete

and it will not give a consistance result because for example tabe Famille is associeted to Tab ARticle and not to Docligne

Re: INNER JOIN Issue Multiple Table

abhijitnalekar — Tue, 21 Sep 2021 07:09:46 GMT

In this case, can you please answer the point

1. Do we have any relationship between fact tables?

2. Do we have similar fields in both tables.

The reason for asking this. We can Join fact tables using the Link table.