Join two transaction table to get aggregate value for each date [link table]

[Bhagwat09]

Hello All

I am novice in the Qlik !

I have two transactional tables with name ALX and DAT. Both Have duplicates dates .Both have two fields Line_haul and avg_line_haul_rate resp. I need to compare this two things in single tabular tables for specific date using master calender.

But 1) In DAT table data is loaded on every thursday and monday. So I need that missing dates. Somehow i was able to get that. with prior avg_line_haul value resp.

2) grouped by function is not working or maybe I can't do that

please find attachments of datamodel ,load script, sample object and QVD 

Data modeltabular form

[ALX_INFO]:

LOAD 
ALX_MILES,
LINE_HAUL,
ACTUAL_PU_DATE,
date(ACTUAL_PU_DATE,'MM/DD/YYYY') as ACTUAL_PU_DATE1,
Date(Date(left(ACTUAL_PU_DATE,10)),'MM/DD/YYYY') as ALX_Date

From "lib://QlikSense (allenlund_nprinting)/QVD Repository\Extractor\Sales\[ALX_OFF_LOAD_HISTORY_INFO].QVD"
(qvd) 
where year(Floor(ACTUAL_PU_DATE))>year(AddYears(date(Today()-1),-2))
;




[DAT]:

LOAD
   
    RATE_DATE,   
   
    AVG_LINEHAUL_RATE,
   
    
Date(Floor(Date(Date#(RATE_DATE, 'YYYY/MM/DD hh:mm:ss'),'MM/DD/YYYY')),'MM/DD/YYYY') as Dat_Date
   
FROM [lib://QVD STORAGE (allenlund_nprinting)/DAT.QVD]
(qvd);


link_date_table:

load ALX_Date as Date //as ALX_Date1
resident ALX_INFO;

Load Dat_Date as Date //as Dat_Date2
Resident DAT;


link_date_table2:
Load Distinct
Date,


Date as ALX_Date,
Date as Dat_Date

Resident link_date_table;
///master calender
MinMaxTable:
Load 
Max(Dat_Date) as Maxdate,
Min(Dat_Date) as Mindate
Resident [DAT];

Let vMax= Peek('Maxdate',0,'MinMaxTable');
Let vMin= Peek('Mindate',0,'MinMaxTable');

Drop Table MinMaxTable;

Calender:
Load
date($(vMin) + RowNo()-1) as X
 AutoGenerate $(vMax) - $(vMin)+1;

 



Master_Calendar:
LOAD X AS Date,

X AS [%Calendar Date],
date(X,'MM/DD/YY (WWW)') AS [Calendar Date_Name],
Date(X,'MM/DD/YYYY') AS [Calendar Date1], 
day(X) AS [%Calendar Day],
Year(X) AS [%Calendar Year],
Month(X) AS [%Calendar Month],
Week(X) AS [%Calendar Week],
WeekDay(X) AS [%Calendar WeekDay],
MonthName(X) AS [Calendar Month_Name],
Num(Month(X)) AS [Calendar Month Num],
QuarterName(X) AS [Calendar QuarterName],
'Q'&Ceil(X/3) AS [%Calendar Quarter],
Date(MonthStart(X)) AS [Month Start],
Date(MonthEnd(X)) AS [Month End],
Date(QuarterStart(X)) AS [Quarter Start],
Date(QuarterEnd(X)) AS [Quarter End],
If(Month(X)<=3,Month(X)+9,Month(X)-3) AS [Fiscal Month Num],
Dual(Month(X),If(Month(X)<=3,Month(X)+9,Month(X)-3)) AS [Fiscal Month],
Year(Yearname(X,0,4))+1 AS [Fiscal Year],
Left(Year(Yearname(X,0,4)),5)&'-'&Right(Year(Yearname(X,0,4))+1,2) AS [FY Year Name]
 Resident Calender

 where (year(Floor(X))>= year(AddYears(date(Today()-1),-2)));


DROP Table Calender;

Please help on this 

I tried my all possible ways

Thanks

Bhagwat K 

[rubenmarin]

Hi, I'm not sure to understand what you want, maybe it's somethinglike the canonical date, check and example here: https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578