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 on BRANCH_ID and DATE3.
I am new to Qlik and would appreciate any assistance to help me join the script below accurately.
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];
