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

Bhagwat09 — Wed, 03 Nov 2021 11:27:40 GMT

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

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

rubenmarin — Sun, 31 Oct 2021 07:53:25 GMT

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

topic Re: Join two transaction table to get aggregate value for each date [link table] in App Development

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

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