topic Join 4 Tables to Calendar (Synthetic Key Issue) in QlikView

Join 4 Tables to Calendar (Synthetic Key Issue)

Chloe19 — Mon, 19 Jul 2021 04:59:32 GMT

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];

Re: Join 4 Tables to Calendar (Synthetic Key Issue)

PiEye — Mon, 19 Jul 2021 16:16:35 GMT

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

(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)

Re: Join 4 Tables to Calendar (Synthetic Key Issue)

Chloe19 — Tue, 20 Jul 2021 01:49:56 GMT

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];