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.
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;
you can do as follow:
Join_Table:
load
Product_ID,
Date,
Quantity as Quantity_Produced
from Produced;
join
load
Product_ID,
Date,
Quantity as Quantity_Sold
from Ordered;
join
load
Product_ID,
Date,
Quantity as Quantity_Stock
from StockLevel;
You have 3 different dates :
This sounds like a classic use case for a Canonical Date as described by HIC in his blog post.
ps. I'd look at concatenating your 3 Fact tables as opposed to joining them.
Yes you are right. I have 3 dates..And would like to Use date as a Dimension and Quantities of the 3 tables as measures..
Still don't know how to effect it .
@Andrea with the above I would not achieve what am looking for..
just change the script as below:
Join_Table:
load
Product_ID,
[Produced Date] as Date,
Quantity as Quantity_Produced
from Produced;
join
load
Product_ID,
[Ordered Date] as Date,
Quantity as Quantity_Sold
from Ordered;
join
load
Product_ID,
[Stock Date] as Date,
Quantity as Quantity_Stock
from StockLevel;
This is what am looked forward to achieve.
The canonical is a classic example and am trying to see if I can achieve it that way. Unfortunately the Join won't give me same data as desired.
what data are you missing using join ?
Am not missing but My Dates are literally of different attributes, hence if I do it as a join, I will be treating them as the same which is not.
Canonical suggest, creation of a Master calendar, which will allow users to select Product quantity produced on certain Date, and sold on certain date. That way I can get all the 3 plotted in the same axis, other wise the join treats the dates as if they are of same attribute.
If I well understand you'd have a unique date field on which you'll create a master calendar.
Is it true ?
No, no unique date field. As for example some products are produced on same date but ordered on different dates and stock level taken on different dates.
While some have same order dates but different production dates and different stock level taking dates..
So the dates are not the same at all (not unique in anyway)
But some are actually the same.