Connection to date and part

[Uygar-Hamburg]

Hi,

I have multiple tables that I have connected through the part/product.

One of these tables are connected to a table that shows dates.

Now my problem is that every table has lots of different dates like due date or order date eg.

When I filter through the panel it does not show the correct amounts.

How can I solve this, I assume by connecting or formatting the dates of each table to match with the date that is

connected to the table that shows dates?

Thank you for your help.

[Vegar]

I would recommend you to start to take a look at this post Canonical Date - Qlik Community - 1463578 by HIC and/or this Linking to two or more dates - Qlik Community - 1495322 by RWunderlich.

If you still need help, then I would recommend you to add some more details about your data modelling issue into this case.

BR Vegar 

[Uygar-Hamburg]

Hi, 

Thank you I tried to apply the solution of the Tutorial Using Common Date Dimensions.

I have created part 2 Link table

But I cannot create the Common Calendar, I get an error :

Do you know how I can solve that? Do I need to remove the AutoCalenda?

Thanks

[Vegar]

I looks like you are calling  for a SUB named CalendarFromField. 

Make sure you have that SUB loaded into your script in prior to the row containing
CALL CalendarFromField(...)

[Uygar-Hamburg]

Hi Vegar, 

thank you I dont have an error anymore.

In order to see the calendar do I need to enter a Master Calendar script

after Sub CalendarFromField?

Now, I cant see the calendar in the data model.

I wrote 

Sub CalendarFromField

CALL CalendarFromField('Date','Calendar','');
CALL CalendarFromField('StatusDate', 'Inv3PLCalendar', 'StatDate');
CALL CalendarFromField('Warehouse', 'ContainerDetCalendar', 'WHS');
CALL CalendarFromField('ShipDate', 'IOFShipDte', 'ShipDTe');
CALL CalendarFromField('PODueDate', 'PODetailsDueDte', 'PODueDte');

Thank you for your help.

[Uygar-Hamburg]

Hi, 

I know it is way to simple but I am a beginner now.

It would be great if someone could help me with this topic.

Thank you for your help.

[Vegar]

I think you missunderstood the concept. Let me try to explain in other words.

CalendarFromField is not a standard Qlik Sense function it is a custom script snipplet stored earlier in the script or in an external script file. If you have a master calendar script file containing a script for creating a  master calendar you need to either include that script into your script or copy that script into your script.

Include method:

$(Include=LIB://ScriptLib\MasterCalendar.qvs);
CALL CalendarFromField('Date','Calendar','')

Copy from master script method:

SUB CalendarFromField(_param1, _param2, _param3)
  //Here comes some script defining your calendar
  //It is this code that is run when using CALL further down.
ENDSUB 
CALL CalendarFromField('Date','Calendar','')

I hope this helps you past your obstacle.

BR Vegar

[Uygar-Hamburg]

Thank you for your support Vegar.

I have this loaded now but it doesn't generate individual fact tables as described in the tutorial.

I think it is again the order of lines, do you have an idea?

DateLink:
LOAD
PartNum
,Warehouse as CALENDAR_DATE
,'WHS' as DateType
RESIDENT v_ContainerDetails;

LOAD
PartNum
,ShipDate as CALENDAR_DATE
,'ShipDte' as DateType
RESIDENT v_InvoicedOpenOrForecasted;

LOAD
PartNum
,PODueDate as CALENDAR_DATE
,'PODueDte' as DateType
RESIDENT v_PODetails;

LET S_DATE_START = NUM(YEARSTART(ADDYEARS(TODAY(), -1)));
LET S_DATE_END = NUM(FLOOR(MONTHEND(TODAY())));

DATE_CALENDAR:
LOAD
CALENDAR_DATE AS %KEY_CALENDAR_DATE,
DATE(CALENDAR_DATE) AS CALENDAR_DATE,
YEAR(CALENDAR_DATE) AS CALENDAR_YEAR,
DUAL(DATE(CALENDAR_DATE, 'MMMM'), NUM(MONTH(CALENDAR_DATE))) AS CALENDAR_MONTH,
NUM(MONTH(CALENDAR_DATE)) AS CALENDAR_MONTH_NUM,
NUM#(DATE(CALENDAR_DATE, 'YYYYMM')) AS CALENDAR_YEAR_MONTH_NUM,
DATE(WEEKSTART(CALENDAR_DATE), 'YYYYMMDD') AS CALENDAR_WEEK_ORDER,
DAY(CALENDAR_DATE) AS CALENDAR_DAY,
DUAL(WEEKDAY(CALENDAR_DATE), NUM(WEEKDAY(CALENDAR_DATE))) AS CALENDAR_WEEK_DAY,
'Q' & CEIL(NUM(MONTH(CALENDAR_DATE))/3) AS CALENDAR_QUARTER,
DUAL(DATE(CALENDAR_DATE, 'MMM-YYYY'),
NUM#(DATE(CALENDAR_DATE, 'YYYYMM'))) AS CALENDAR_YEAR_MONTH,
DUAL(NUM(DAY(WEEKSTART(CALENDAR_DATE)), '00') &'/'& DATE(WEEKEND(CALENDAR_DATE), 'DD-MMM-YYYY'),
YEAR(WEEKSTART(CALENDAR_DATE)) & NUM(WEEK(WEEKSTART(CALENDAR_DATE)), '00')) AS CALENDAR_WEEK;
LOAD
($(S_DATE_START) + RECNO()) - 1 AS CALENDAR_DATE
AUTOGENERATE((S_DATE_END - S_DATE_START) + 1);

SET S_DATE_START=;
SET S_DATE_END=;

Sub CalendarFromField

CALL CalendarFromField('CALENDAR_DATE','Calendar','');

CALL CalendarFromField('Warehouse', 'ContainerDetCalendar', 'WHS');
CALL CalendarFromField('ShipDate', 'IOFShipDte', 'ShipDTe');
CALL CalendarFromField('PODueDate', 'PODetailsDueDte', 'PODueDte');
EndSub