Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

michael_anywar
Contributor

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
Contributor

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

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;

18 Replies
agigliotti
Honored Contributor II

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

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;


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

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
Contributor

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

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
Honored Contributor II

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

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
Contributor

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

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
Honored Contributor II

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

what data are you missing using join ?

michael_anywar
Contributor

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

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
Honored Contributor II

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

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

Is it true ?

michael_anywar
Contributor

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

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.

Community Browser