Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
michael_anywar
Creator
Creator

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

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_IDQuantityDateName
P1342/1/2018PRO
P247712/2/2018PRO2
P286

2/11/2017

PRO2

Ordered

order idProduct_IDQuantityDateName
id_1P111011/3/2018PRO
id_2P2419/3/2018PRO2
id_3P220

2/11/2017

PRO2

StockLevel

Product_IDQuantityDateName
P1342/4/2018PRO
P247712/3/2018PRO2
P286

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.




1 Solution

Accepted Solutions
michael_anywar
Creator
Creator
Author

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;

View solution in original post

18 Replies
agigliotti
Partner - Champion
Partner - Champion

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;


Anonymous
Not applicable

You have 3 different dates :

  • Produced Date
  • Ordered Date
  • Stock Level Date

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.

michael_anywar
Creator
Creator
Author

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..

agigliotti
Partner - Champion
Partner - Champion

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;

michael_anywar
Creator
Creator
Author

This is what am looked forward to achieve.

picds.png

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.

agigliotti
Partner - Champion
Partner - Champion

what data are you missing using join ?

michael_anywar
Creator
Creator
Author

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.

agigliotti
Partner - Champion
Partner - Champion

If I well understand you'd have a unique date field on which you'll create a master calendar.

Is it true ?

michael_anywar
Creator
Creator
Author

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.