Re: Join 4 Tables to Calendar (Synthetic Key Issue... - Qlik Community

Chloe19 · ‎2021-07-19

Hi

I have 4 Tables, all 4 link to table 1 by BRANCH_ID to identify the Region which only Table 1 holds.

HOWEVER, 3 of the tables have a Date field (Date 3) which I am trying to join to the Master Table.

No matter what join I try I always get a synthetic key.

I am new to Qlik and would appreciate any assistance to help me join the script below accuratley.

BSB:
//MAIN TABLE TO IDENTIFY THE LOCATION OF THE BRANCH - SQL (REST OF THE TABLES ARE EXCEL)
LOAD
"Region",
"BSB" as BRANCH_ID
;

SELECT DISTINCT
LEFT(BSBCC,4) BSB
,B.[New Region] as Region

FROM OEReporting.[dbo].BranchNetworkAmitWithAdmin B

WHERE B.[New Region] is not null;

STORE BSB INTO [lib://RBI_SourceData/QVD/RawQVD/OE/BSB.QVD];



CASH:
// TABLE 2 - BRANCH IS NEEDS TO LINK TO TABLE 1

LOAD
BRANCH_ID,
TRAN_TYPE,
DATE3,
TOTAL_VAL,
TOTAL_CASH
FROM [lib://RBI_SourceData/App/RefFiles/OEReporting/SUAR/Combined.xlsx]
(ooxml, embedded labels, table is Cash);



DAILY:
// TABLE 3 - BRANCH IS NEEDS TO LINK TO TABLE 1
LOAD


DATE3,
BRANCH_ID,
C_5_10,
C_10_20,
D_5_10,
D_10_20


FROM [lib://RBI_SourceData/App/RefFiles/OEReporting/SUAR/Combined.xlsx]
(ooxml, embedded labels, table is [Daily High Value]);



SUAR:
// TABLE 4 - BRANCH IS NEEDS TO LINK TO TABLE 1
LOAD
Sort,
DATE3,
BRANCH_BSB as BRANCH_ID,
SUARS
FROM [lib://RBI_SourceData/App/RefFiles/OEReporting/SUAR/Combined.xlsx]
(ooxml, embedded labels, table is SUAR);



//------------------------------ NEED TO JOIN THE ABOVE DATE3 FIELDS TO THE MASTER TABLE BELOW HOWEVER I ALWAYS GET SYNTHETIC KEY 



// Date Dimension


// to load Quarters Full Name
QuarterNAME:

LOAD * Inline [
Quarter , QuarterFullName
Q1 ,FIRST
Q2 ,SECOND
Q3 ,THIRD
Q4 ,FOURTH
];


// to create Quarters ie Q1,Q2
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);


varMinDate = num(date(mid('2020-11-01',1,10 ),'YYYY-MM-DD'));
// varMaxDate = num(date('2017-01-10','YYYY-MM-DD'));
varMaxDate = num(date(today(),'YYYY-MM-DD'));

// Creating a Temporary Calendar

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 as Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

// Date Dimension

MasterCalendar:

LOAD
*,
[Quarter Number]-1 as PQ,
//if(LastQtrFlag=1 and len(QuarterEnd([Effective Date]))>0,1,0) as lastQtrMnth,


AutoNumber ([Year Month Num], 'PeriodID') as PeriodID,
AutoNumber (Year & Quarter, 'QuarterID') as QuarterID;

Load

trim(date(TempDate,'YYYYMMDD')) as [DateID],
(date(TempDate,'MM/DD/YYYY')) as [Effective Date],
(date(TempDate,'MM/DD/YYYY')) as [Date3],
// date(TempDate,'MM/DD/YYYY') as [Another Date],

if(month(QuarterEnd(TempDate))=Month(TempDate),1,0) as [LastMnthPerQtr],

day(TempDate) as Day,
TempDate as [US Calendar Format],
date(TempDate,'DD/MM/YYYY') as [UK Calendar Format],
date(TempDate,'WWWW') as [Full Day Name],
year(TempDate) as Year,
inyear(TempDate,today(),0) * -1 as [CY], // Current Year
inyear(TempDate,today(),-1) * -1 as [First PY],
inyear(TempDate,today(),-2) * -1 as [Second PY],
inyeartodate(TempDate,today(),0) * -1 as [CYTD],
inyeartodate(TempDate,today(),-1) * -1 as [First PYTD],
inyeartodate(TempDate,today(),-2) * -1 as [Second PYTD],
if(InYearToDate(TempDate, today(),0), 1, 0) as CurYearFlag,
if(InQuarterToDate(TempDate, today(),0), 1, 0) as CurQtrFlag,
if(InMonthToDate(TempDate, today(),0), 1, 0) as CurMonthFlag,
if(InYear(TempDate, today(),-1), 1, 0) as LastYearFlag,
if(InQuarter(TempDate, today(),-1), 1, 0) as LastQtrFlag,
if(InMonth(TempDate, today() ,-1), 1, 0) as LastMonthFlag,
if(InMonthToDate(TempDate, today(),-2), 1, 0) as LastQtrLastMonthFlag,


ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Ceil(Month(TempDate)/3) as [Quarter Number],
quarterName(TempDate) as [Quarter Name],
yearname(TempDate) & 'Q' & Ceil(Month(TempDate)/3) as [Quarter Year],

inquarter(TempDate,today(),0) * -1 as [CQ], // Current Quarter
// if(InQuarter(TempDate, today(),-1), 1, 0) as [PQ1], // Previous Quarter
inquarter(TempDate,today(),-4) * -1 as [First PQ],
inquarter(TempDate,today(),-8) * -1 as [Second PQ],
inquartertodate(TempDate,today(),0) * -1 as [CQTD],
inquartertodate(TempDate,today(),-4) * -1 as [First PQTD],
inquartertodate(TempDate,today(),-8) * -1 as [Second PQTD],
date(monthstart(TempDate),'MM') as [Month Number],
num(month(TempDate)) as Num_Month,
month(TempDate) as Month,
date(monthstart(TempDate),'MMMM') as [Month Full Name],
monthstart(TempDate) as [Calendar Month Start Date],
monthend(TempDate) as [Calendar Month End Date],
date(monthstart(TempDate), 'MMM-YYYY') as [Month Year],
date(monthstart(TempDate), 'YYYYMM') as [Year Month Num],

week(TempDate) as Week,
week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [Week Year],
week(weekstart(TempDate)) & '-' & Month(TempDate) as [Week Month],
weekDay(TempDate) as [Week Day],
WeekEnd(TempDate) as WEEKENDS,


If( TempDate > monthstart(addmonths(today(),-1)) and TempDate <= today(),1) as [Rolling 12],
If( TempDate > monthstart(addmonths(today(),-1)) and TempDate <= today(),1) as [Rolling 3]

Resident TempCalendar
Order By TempDate ASC;

Drop Table TempCalendar;
Drop Table QuarterNAME;

STORE [MasterCalendar] INTO [lib://RBI_SourceData/QVD/RawQVD/OE/MasterCalendar.QVD];

PiEye · ‎2021-07-19

Hi Chloe, and welcome to Qlik!

As you've noticed, Qlik automatically performs a join in the front end between fields that have the same name.

What you have here are 3 tables with two fields that are the same - Branch and date3. Qlik will attempt to handle this by creating a "synthetic key" which is really a table comprising combinations of both those values, and this is what you are seeing in the data model.

Synthetic keys, while they do work in most cases are best avoided for reasons I wont go into on this post, but you will find lots of information in the community.

So - how do we manage this in the data model? Probably the best approach here is to setup your tables as such:

Main table: contains a branch/Date key field, Branch ID, DateID (that links to your calendar)

Cash, Daily and Suar: the same concatenated branch/ date key field and no date field

Branch & Calendar: no change

So your load will look like this for each of the 3 tables, using CASH as an example

CASH:
LOAD

BRANCH_ID&(DATE3*1) AS ky_BranchDate,
//BRANCH_ID, //<--- commented out so it doesn't load into the table
TRAN_TYPE,
//DATE3, //<--- commented out so it doesn't load into the table
TOTAL_VAL,
TOTAL_CASH

....

You will then need to do similar into your main/fact table (I can't see it in the above code) so that the 3 tables have a value to join onto for ky_BranchDate

Hope this helps

Pi

(As an aside: There's also a handy function called Autonumber(...) that's a more efficient way of creating the keys, but I wanted to focus on the basics here.. let me know if that works for you)

Chloe19 · ‎2021-07-19

Hi PiEye

Thanks for taking the time to look at my problem, much appreciated.

I did understand what you meant with the key to link the 3 tables (Cash/Daily/SUAR) however I don't know how I would link it to the BSB table to find the Region as the BSB table does not contain a date just the BRANCH_ID.

I have attached a picture of the model, which linked the 3 tables but need some guidance to link to to BSB and the Calendar (blue/red in the photo)

Current:

BSB:
//MAIN TABLE TO IDENTIFY THE LOCATION OF THE BRANCH - SQL (REST ARE EXCEL)
LOAD
"Region",
"BSB" as BRANCH_ID
;

SELECT DISTINCT 
LEFT(BSBCC,4) BSB

,B.[Region]

FROM OEReporting.[dbo].BranchNetworkAmitWithAdmin B

WHERE B.[New Region] is not null;

CASH:
LOAD

BRANCH_ID&(DATE3*1)    AS ky_BranchDate,    // <-------------------KEY
//BRANCH_ID, //<--- commented out so it doesn't load into the table
TRAN_TYPE,
//DATE3, //<--- commented out so it doesn't load into the table
TOTAL_VAL,
TOTAL_CASH



FROM [lib://RBI_SourceData/App/RefFiles/OEReporting/SUAR/Combined.xlsx]
(ooxml, embedded labels, table is Cash);

STORE BSB INTO [lib://RBI_SourceData/QVD/RawQVD/OE/BSB.QVD];


DAILY:
// TABLE 3 - BRANCH ID NEEDS TO LINK TO TABLE 1
LOAD
//    DATE3 as DATED ,
//    BRANCH_ID,"Branch"
    BRANCH_ID&(DATE3*1)    AS ky_BranchDate,   // <------------KEY
    C_5_10,
    C_10_20,
    D_5_10,
    D_10_20
FROM [lib://RBI_SourceData/App/RefFiles/OEReporting/SUAR/Combined.xlsx]
(ooxml, embedded labels, table is [Daily High Value]);



SUAR:
// TABLE 4 - BRANCH IS NEEDS TO LINK TO TABLE 1
LOAD
Sort,
//DATE3,
//BRANCH_BSB as BRANCH_ID,
    BRANCH_BSB&(DATE3*1)    AS ky_BranchDate, // <----------------KEY


SUARS
FROM [lib://RBI_SourceData/App/RefFiles/OEReporting/SUAR/Combined.xlsx]
(ooxml, embedded labels, table is SUAR);











// Date Dimension


// to load Quarters Full Name
 QuarterNAME:
 
 LOAD * Inline [
 Quarter		, QuarterFullName
 Q1				,FIRST
 Q2				,SECOND
 Q3				,THIRD
 Q4				,FOURTH
 ];
 
 
 // to create Quarters ie Q1,Q2
 QuartersMap:  
    MAPPING LOAD   
    rowno() 				as Month,  
    'Q' & Ceil (rowno()/3)  as Quarter  
    
    AUTOGENERATE (12);  

      
       varMinDate = num(date(mid('2020-11-01',1,10 ),'YYYY-MM-DD'));
//       varMaxDate = num(date('2017-01-10','YYYY-MM-DD'));
       varMaxDate = num(date(today(),'YYYY-MM-DD'));

// Creating a Temporary Calendar
      
TempCalendar:  
    LOAD  
                   $(varMinDate) + Iterno()-1 			as Num,  
                   Date($(varMinDate) + IterNo() - 1) 	as TempDate  
                   AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);  
   
// Date Dimension

MasterCalendar:  

LOAD
	*,
	[Quarter Number]-1 					as PQ,
	//if(LastQtrFlag=1 and len(QuarterEnd([Effective Date]))>0,1,0) 					as lastQtrMnth,
	


	AutoNumber ([Year Month Num], 'PeriodID') 											as PeriodID,
	AutoNumber (Year & Quarter, 'QuarterID')											as QuarterID;
	
Load  
                
	trim(date(TempDate,'YYYYMMDD'))														as [DateID],
	(date(TempDate,'MM/DD/YYYY')) 														as [Effective Date],
    (date(TempDate,'MM/DD/YYYY')) 														as [Date3],
 //     date(TempDate,'MM/DD/YYYY') 														as [Another Date],
	
	if(month(QuarterEnd(TempDate))=Month(TempDate),1,0) 			as [LastMnthPerQtr],
	
    day(TempDate) 																		as Day,
    TempDate 																			as [US Calendar Format], 
    date(TempDate,'DD/MM/YYYY') 														as [UK Calendar Format], 
    date(TempDate,'WWWW') 																as [Full Day Name], 
    year(TempDate) 																		as Year,
    inyear(TempDate,today(),0) * -1  													as [CY],    // Current Year
    inyear(TempDate,today(),-1) * -1 													as [First PY],
    inyear(TempDate,today(),-2) * -1 													as [Second PY],
    inyeartodate(TempDate,today(),0) * -1  												as [CYTD],
    inyeartodate(TempDate,today(),-1) * -1 												as [First PYTD],
    inyeartodate(TempDate,today(),-2) * -1 												as [Second PYTD], 
      if(InYearToDate(TempDate, today(),0), 1, 0) as CurYearFlag,
if(InQuarterToDate(TempDate, today(),0), 1, 0) as CurQtrFlag,
if(InMonthToDate(TempDate, today(),0), 1, 0) as CurMonthFlag,
if(InYear(TempDate, today(),-1), 1, 0) as LastYearFlag,
if(InQuarter(TempDate, today(),-1), 1, 0) as LastQtrFlag,
if(InMonth(TempDate, today() ,-1), 1, 0) as LastMonthFlag,
if(InMonthToDate(TempDate, today(),-2), 1, 0) as LastQtrLastMonthFlag,

    
    ApplyMap('QuartersMap', month(TempDate), Null()) 									as Quarter,
    
    Ceil(Month(TempDate)/3)																as [Quarter Number],
    quarterName(TempDate) 																as [Quarter Name],
    yearname(TempDate) & 'Q' & Ceil(Month(TempDate)/3)  								as [Quarter Year], 
    
    inquarter(TempDate,today(),0) * -1 													as [CQ],  // Current Quarter
    //    if(InQuarter(TempDate, today(),-1), 1, 0) 											as [PQ1],  // Previous Quarter
    inquarter(TempDate,today(),-4) * -1 												as [First PQ],
    inquarter(TempDate,today(),-8) * -1 												as [Second PQ],  
    inquartertodate(TempDate,today(),0) * -1 											as [CQTD],
    inquartertodate(TempDate,today(),-4) * -1 											as [First PQTD],
    inquartertodate(TempDate,today(),-8) * -1 											as [Second PQTD], 
    date(monthstart(TempDate),'MM') 													as [Month Number], 
    num(month(TempDate))																as Num_Month,
    month(TempDate) 																	as Month,                   
    date(monthstart(TempDate),'MMMM') 													as [Month Full Name],
    monthstart(TempDate) 																as [Calendar Month Start Date],
    monthend(TempDate) 																	as [Calendar Month End Date],
	date(monthstart(TempDate), 'MMM-YYYY') 												as [Month Year], 
	date(monthstart(TempDate), 'YYYYMM') 												as [Year Month Num],

    week(TempDate) 																		as Week, 
    week(weekstart(TempDate)) & '-' & WeekYear(TempDate)								as [Week Year],
    week(weekstart(TempDate)) & '-' & Month(TempDate)   								as [Week Month],
    weekDay(TempDate) 																	as [Week Day],
WeekEnd(TempDate) as WEEKENDS, 

      
    If( TempDate > monthstart(addmonths(today(),-1)) and TempDate <= today(),1) 		as [Rolling 12],
    If( TempDate > monthstart(addmonths(today(),-1)) and TempDate <= today(),1) 		as [Rolling 3]
                
Resident TempCalendar  
Order By TempDate ASC;  

Drop Table 	TempCalendar; 
Drop Table  QuarterNAME;

STORE [MasterCalendar] INTO [lib://RBI_SourceData/QVD/RawQVD/OE/MasterCalendar.QVD];

Join 4 Tables to Calendar (Synthetic Key Issue)

Application Development

Scripting