topic Re: Breaking Table Joins in QlikView

Breaking Table Joins

nadkalyan — Thu, 12 Jul 2012 14:52:53 GMT

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

Re: Breaking Table Joins

edg_baltazar — Thu, 12 Jul 2012 15:46:16 GMT

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

Re: Breaking Table Joins

Thu, 12 Jul 2012 15:52:44 GMT

paste script please

Re: Breaking Table Joins

nadkalyan — Thu, 12 Jul 2012 19:55:45 GMT

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

Re: Breaking Table Joins

Fri, 13 Jul 2012 08:05:57 GMT

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

Thanks

Re: Breaking Table Joins

nadkalyan — Fri, 13 Jul 2012 13:16:48 GMT

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