Help with joining multiple tables while comparing dates (Date In)

[michael_anywar]

Hello, Help with Join involving dates and multiple tables. I would like to Create a join of 3 tables linking them with the Product_ID. and using the date

Produced

Product_ID	Quantity	Date	Name
P1	34	2/1/2018	PRO
P2	477	12/2/2018	PRO2
P2	86	2/11/2017	PRO2

Ordered

order id	Product_ID	Quantity	Date	Name
id_1	P11	10	11/3/2018	PRO
id_2	P2	4	19/3/2018	PRO2
id_3	P2	20	2/11/2017	PRO2

StockLevel

Product_ID	Quantity	Date	Name
P1	34	2/4/2018	PRO
P2	477	12/3/2018	PRO2
P2	86	11/1/2017	PRO2

Expected Table after Join.

Join_Table:

Product_ID;

Quantity_Produced;

Quantity_Sold;

Quantity_Stock;

Date

The reason i need the date is because I would use it to filter/ create a dimension using it for Visualization. This date would also be used in the date Picker dimension. Thank you.

[michael_anywar]

@Andrea Does that explain it..?

[agigliotti]

if you need to have three different dates, I suggest you to create a calendar for each of them.

but in this way you'll not able to have for a specific date, the three quantity measures on the same row.

[Anonymous]

Andrea:      Your are correct, as I suggested to Michael earlier I'd look at concatenating your 3 Fact tables as opposed to joining them, and HIC's blog post Canonical Date  suggests multiple calendars.

Michael:       The first time I read the blog post it took me a few reads to fully understand it, so maybe make yourself a cup of tea to sip whilst you are sussing it.

[michael_anywar]

Am on it Bill. Trying to interpret see how the concatenated table will look like and the after see how the calendar comes into force.

[Anonymous]

Maybe grab some chocolate cake as well.

[michael_anywar]

No progress yet..

[michael_anywar · Accepted solution]

fredon78 bill.markham youssefbelloum agigliotti‌

Am still struggling with creation of Master calendar gives me an error on load. Says DateID not found yet I defined it

Script for first Table master calendar. Anybody can help with this

///////////////////////////////////////////////////////////////////////////////////////////////////////

All my Dates have to be converted with Timestamp(ConvertToLocalTime((25569 + Round([ts]/1000) / 86400),'GMT+02:00')) AS [RecordDate]

////////////////////////////////////////////////////////////////////////////////////////////////////////////

LIB CONNECT TO 'GetWarehouse';

Stockshit:

SQL SELECT

"itemDirectoId",

"ts",

"summedQuantity"

FROM JSON (wrap on) "root";

[DailyStock]:

LOAD [itemDirectoId] AS [Product_Id],

Timestamp(ConvertToLocalTime((25569 + Round([ts]/1000) / 86400),'GMT+02:00')) AS [RecordDate],

[summedQuantity] AS [summedQuantity]

RESIDENT Stockshit;

NEXT startAt;

//Creation of master calendar starts here

tmp:

LOAD

min(Timestamp(ConvertToLocalTime((25569 + Round([ts]/1000) / 86400),'GMT+02:00'))) as [MinDate],

max(Timestamp(ConvertToLocalTime((25569 + Round([ts]/1000) / 86400),'GMT+02:00'))) as [MaxDate]

Resident Stockshit;

LET Start = floor(YearStart(peek('MinDate')));

LET End = floor(YearEnd(peek('MaxDate')));

Drop Table tmp;

LET NumOfDays = End - Start + 1;

Date_src:

LOAD

$(Start) + Rowno() -1 as [DateId]

AUTOGENERATE $(NumOfDays);

StockCalendar:

LOAD

DateId,

date(DateId) as [Date],

day(DateId) as [Day],

week(DateId) as [Week],

month(DateId) as [Month], // simple month name; it is dual - numeric and text

dual(month(DateId) & '-' & year(DateId),

year(DateId) & num(month(DateId), '00')) as MonthYear, // Month-Year format, dual

year(DateId) as Year,

weekday(DateId) as Weekday,

'Q' & ceil(month(DateId)/3) as Quarter // in format Q1, Q2, Q3, Q4

RESIDENT Date_src;

Drop Table Date_src;

DROP TABLE Stockshit;

[agigliotti]

remember to take care of Qlik case sensitive feature.

"DateID" is not equal to "DateId".

[michael_anywar]

Thank you . The first Master calendar Create