Breaking Table Joins

[nadkalyan]

Hi

I have 4 tables in qlikview - 'TotalSpend'. 'Delinquency', 'Employee', 'DimDate' with following Keys in each table

'TotalSpend'  = EmployeeID, ChargeDate

'Delinquency' = EmployeeID, ChargeDate

Employee = EmployeeID

DimDate= ChargeDate

I would like to join  TotalSpend.EmployeeID<-->Employee.EmployeeID

                            TotalSpend.ChargeDate<-->DimDate.ChargeDate

                              Delinquency.EmployeeID<-->Employee.EmployeeID

                              Delinquency.ChargeDate<-->DimDate.ChargeDate

But when I Loaded these Tables to Qlikview here is how joins are coming up

As you can see Qlikview has created $Syn1 join between 'TotalSpend' and "Delinquency' Tables. I would like to break this join so I have the desired join as I mentioned earlier.

The reason I need to do this is when i pull data from 'TotalSpend' and 'Delinquency' tables into a chart or straight table in qlikview only Employees that are present in both the tables are only showing up, Any employee who is in 'TotalSpend' table but not in 'Delinquency' table is not showing up and vice versa.

Does anyone have any suggestions on how can we model this?

Thanks
Kalyan

[edg_baltazar · Accepted solution]

Hi Kalyan!,You have to make a Link_Table, Regards!

[#3]

paste script please

[nadkalyan]

Thanks Edgar,

Link_Table worked perfect for our scenario.

For anyone looking for solution - I created a Link_Table with Keys( combi key of employeeID&ChargeDate) from TotalSpend and Delinquency Table to join to Employee and Date Dimensions.

Kalyan

[#5]

Could you please upload your final table view.......and also script if possible.

Thanks

[nadkalyan]

Here you go arun-

We have loaded the tables in seperate tabs, so not sure how I can provide Here is the script for LinkTable-

Link_Table:

Load distinct

TotalSpendDate&TotalSpendEmployeeID as Key,

TotalSpendDate as DateKey,

//YearMonthDate as ChargeTransactionDate,

TotalSpendEmployeeID as EmployeeIDKey

Resident AmexTotalSpend;

Concatenate

LOAD distinct

DelinquencyDate&DelinquencyEmployeeID as Key,

DelinquencyDate as DateKey,

//YearMonthDate as ChargeTransactionDate,

DelinquencyEmployeeID as EmployeeIDKey

Resident AmexDelinquency;

Concatenate

Load Distinct

RetailDate&RetailEmployeeID as Key,

RetailDate as DateKey,

RetailEmployeeID as EmployeeIDKey

Resident AmexRetailSpend;

Concatenate

Load Distinct

AirNotBookedAgencyDate&AirNotBookedAgencyEmployeeID as Key,

AirNotBookedAgencyDate as DateKey,

AirNotBookedAgencyEmployeeID as EmployeeIDKey

Resident AmexAir_NotBookedwithAgency;

Thanks
Kalyan