Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
@Andrea Does that explain it..?
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.
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.
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.
Maybe grab some chocolate cake as well.
No progress yet..
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;
remember to take care of Qlik case sensitive feature.
"DateID" is not equal to "DateId".
Thank you . The first Master calendar Create