Solved: Help with joining multiple tables while comparing ... - Qlik Community

michael_anywar · ‎2018-03-15

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 · ‎2018-03-20

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

agigliotti · ‎2018-03-15

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 · ‎2018-03-15

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 · ‎2018-03-15

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 · ‎2018-03-15

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 · ‎2018-03-15

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.

agigliotti · ‎2018-03-15

what data are you missing using join ?

michael_anywar · ‎2018-03-15

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 · ‎2018-03-15

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

Is it true ?

michael_anywar · ‎2018-03-15

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.

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