Inner join 2 tables on multiple conditions

amber2000 — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone,

I would like to create an Inner Join between table Fact_PickingAssignments: and table TimeRegistration:

The Inner Join is written in the table TimeRegistration and when I debug I get the Error: Ambiguous Column Name D_SK_Employee.

Can somenone see what I'm doing wrong here please? This is the first time I use a join and I have to say that table1(Fact_PickingAssignments) is a big table so I don't want to slow down performance.

Calendar:

Load *,

Date(DayStart([D_SK_Date_Start])) As [Date_Picking],

Year([D_SK_Date_Start]) As [Date_Year],

Month([D_SK_Date_Start]) As [Date_Month],

Week([D_SK_Date_Start]) As [Date_Week],

WeekDay([D_SK_Date_Start]) As [Week_Day],

'Q' & Ceil(Month(D_SK_Date_Start)/3,1,1) AS [Quarter],

Date(Monthstart([D_SK_Date_Start]),'MM-YYYY') As [Date_MonthYear];

//**************************************************************************************************************

// Table 1 *

//**************************************************************************************************************

Fact_PickingAssignments:

LOAD

[D_SK_PickingAssignment] AS [SK_PickingAss],

[BK_WarehouseCode] AS [Warehouse_Code],

[BK_PickingAssignmentID] AS [PA_Ass_ID],

Date(Date#([D_SK_Date_Start],'YYYYMMDD')) AS [D_SK_Date_Start],

Date(Date#([D_SK_Date_Start],'YYYYMMDD')) AS [PA_TR_StartDate],

Date(Date#([D_SK_Date_End],'YYYYMMDD')) AS [PA_EndDate],

Time#([D_SK_Time_Start],'hhmmss') AS [PA_StartTime],

Time#([D_SK_Time_End],'hhmmss') AS [PA_EndTime],

Interval([SecondsWorked] /86400) AS [PA_SecondsWorked],

[D_SK_Employee] AS [Emp_ID],

[FullPallets] AS [PA_FullPallets];
SQL SELECT

[D_SK_PickingAssignment],

[BK_WarehouseCode],

[BK_PickingAssignmentID],

[D_SK_Date_Start],

[D_SK_Date_End],

[D_SK_Time_Start],

[D_SK_Time_End],

[SecondsWorked],

[D_SK_Employee],

[FullPallets]

FROM NVL_DWH.dbo."DW_F_PickingAssignment";

//**************************************************************************************************************

// Table 2 *

//**************************************************************************************************************

TimeRegistration:

LOAD

[D_SK_TimeRegistration] as TR_ID,

[BK_DatabaseCodeGPS] as TR_BK_DatabaseCodeGPS,

[BK_EmployeeID] as TR_GPS_PersonnelID,

[BK_ClockIn] as TR_BK_ClokIn,

[BK_TimeRegistrationCode] as TR_CD_Register,

[TimeRegistrationSeconds] as TR_Num_Minutes,

[D_SK_Date_Planning] as TR_Date_PlanningGPS,

[D_SK_Date_In] as TR_DateClockIn,

[D_SK_Date_Out] as TR_DateClockOut,

[D_SK_Time_In] as TR_Clock_In,

[D_SK_Time_Out] as TR_Clock_Out,

[D_SK_Employee] as Emp_ID;

SQL SELECT

[D_SK_TimeRegistration],

[BK_DatabaseCodeGPS],

[BK_EmployeeID],

[BK_ClockIn],

[BK_TimeRegistrationCode],

[TimeRegistrationSeconds],

[D_SK_Date_Planning],

[D_SK_Date_In],

[D_SK_Date_Out],

[D_SK_Time_In],

[D_SK_Time_Out],

[D_SK_Employee]

FROM "NVL_DWH".dbo."DW_F_TimeRegistration" tr

Inner join "NVL_DWH".dbo.DW_F_PickingAssignment pa

on tr.D_SK_Employee = pa.D_SK_Employee

and pa.D_SK_Date_Start between tr.D_SK_Date_In and tr.D_SK_Date_Out

and pa.D_SK_Time_Start between tr.D_SK_Time_In and tr.D_SK_Time_Out

and tr.TimeRegistrationCode = 'Totaal';

//**************************************************************************************************************

// Dimension *

//**************************************************************************************************************

Employee:

LOAD

[D_SK_Employee] AS [Emp_ID],

Upper(Text([BK_UserName])) AS [Emp_Usys_UserName],

[GPS_UserName] AS [Emp_GPS_UserName],

[ID_Employee_GPS] AS [Emp_GPSPersonnelID],

[Active] AS [Emp_UsysActive];

SQL SELECT

[D_SK_Employee],

[BK_UserName],

[GPS_UserName],

[ID_Employee_GPS],

[Active]

FROM NVL_DWH.dbo."DW_D_Employee";

Kind regards,

Monique

Re: Inner join 2 tables on multiple conditions

Anil_Babu_Samineni — Tue, 11 Oct 2016 09:51:42 GMT

Try This

Fact_PickingAssignments:

Load * Table1;

TimeRegistration:

Load * Table2;

Left Join(TimeRegistration)

Employee:

LOAD

[D_SK_Employee] AS [Emp_ID],

Upper(Text([BK_UserName])) AS [Emp_Usys_UserName],

[GPS_UserName] AS [Emp_GPS_UserName],

[ID_Employee_GPS] AS [Emp_GPSPersonnelID],

[Active] AS [Emp_UsysActive];

SQL SELECT

[D_SK_Employee],

[BK_UserName],

[GPS_UserName],

[ID_Employee_GPS],

[Active]

FROM NVL_DWH.dbo."DW_D_Employee";

Re: Inner join 2 tables on multiple conditions

amber2000 — Tue, 11 Oct 2016 10:03:18 GMT

Where do I use the conditions?

Why a Left Join and not an Inner join? I want only those records who have a match in both tables.

Re: Inner join 2 tables on multiple conditions

sunny_talwar — Tue, 11 Oct 2016 10:17:31 GMT

May be you need to specify which table you are picking D_SK_EMPLOYEE from? In fact I would do this for all my fields just to be sure.

SQL SELECT

[D_SK_TimeRegistration],

[BK_DatabaseCodeGPS],

[BK_EmployeeID],

[BK_ClockIn],

[BK_TimeRegistrationCode],

[TimeRegistrationSeconds],

[D_SK_Date_Planning],

[D_SK_Date_In],

[D_SK_Date_Out],

[D_SK_Time_In],

[D_SK_Time_Out],

[pa.D_SK_Employee] or [tr.D_SK_Employee]

FROM "NVL_DWH".dbo."DW_F_TimeRegistration" tr

Inner join "NVL_DWH".dbo.DW_F_PickingAssignment pa

on tr.D_SK_Employee = pa.D_SK_Employee

and pa.D_SK_Date_Start between tr.D_SK_Date_In and tr.D_SK_Date_Out

and pa.D_SK_Time_Start between tr.D_SK_Time_In and tr.D_SK_Time_Out

and tr.TimeRegistrationCode = 'Totaal';

Re: Inner join 2 tables on multiple conditions

amber2000 — Tue, 11 Oct 2016 14:31:56 GMT

This is exactly what I wanted to do.

Thanks a million Sunny

topic Re: Inner join 2 tables on multiple conditions in QlikView

Inner join 2 tables on multiple conditions

Re: Inner join 2 tables on multiple conditions

Re: Inner join 2 tables on multiple conditions

Re: Inner join 2 tables on multiple conditions

Re: Inner join 2 tables on multiple conditions