topic Help me on my issue in QlikView

Help me on my issue

devaraj28 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

i have four Date columns like Offer_StartDate

Offer_EndDate

Calendar_StartDate

Calendar_EndDate.

Offer_StartDate & offer_EndDate are in T1 table

Calendar_Startdate & Calendare_EndDate are in T2 table

i have created one Calendar_StartDate calendar object And one Calendar_EndDate calendar object

As per my requirement, i want show the Offer_StartDate and Offrer_EndDate with in the date rang which i select in Calendar_StartDate and Calendar_EndDate, but issue is "Calendar_Startdate & Calendar_EndDate" column table is independent table(isolated table). can u help me how the apply the triggers to make it work this.

Thanks in Advance.

Re: Help me on my issue

Vertig0 — Thu, 20 Feb 2020 16:14:14 GMT

Hi Devaraj28,

I suggest to abandon the Calendar_StartDate and Calendar_EndDate calendar objects completely and create a Master Calendar instead.

Basically you have to go through the following steps:

1. Acquire range

2. Generate temporary calendar

3. Generate master calendar

Since I don't have your data model details, I will give you an example with a table ''Megawatt_hoursByDay'' with production dates ('ProdDate' column). Look at the logic and replace with your own data.

In details:

Step1. Ascertain the date range required

StartAndEndDates:
LOAD
MIN(ProdDate) AS FirstProdDate,
MAX(ProdDate) AS LastProdDate
RESIDENT Megawatt_hoursByDay;

// Store the start and end dates as variables:
LET vFirstDate = NUM(PEEK('FirstProdDate', 0, 'StartAndEndDates'));
LET vLastDate = NUM(PEEK('LastProdDate', 0, 'StartAndEndDates'));

Step2. Generate a temporary calendar, using the date range boundaries acquired
TempCal:
LOAD
DATE($(vFirstDate) + ROWNO() -1) AS TempDate
AUTOGENERATE
$(vLastDate) - $(vFirstDate) + 1;

3. Generate master calendar

MasterCalendar:
LOAD
TempDate AS OrderDate,
WEEK(TempDate) AS Week,
YEAR(TempDate) AS Year,
MONTH(TempDate) AS Month,
DAY(TempDate) AS Day,
WEEKDAY (TempDate) AS Weekday,
'Q' & CEIL(MONTH(TempDate) / 3) AS Quarter,
DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,
WEEK(TempDate) & '-' & YEAR(TempDate) AS WeekYear
RESIDENT TemporaryCalendar;
DROP TABLE TemporaryCalendar;

I hope this is helpful!

Regards,

Re: Help me on my issue

Vertig0 — Thu, 20 Feb 2020 16:24:47 GMT

P.S. Actually you can still utilize calendar objects if that is the requirement. Just use the aliases from the Master Calendar.

Re: Help me on my issue

devaraj28 — Sun, 23 Feb 2020 08:39:44 GMT

Hi,

Thanks for your replay, i have attached the data model screenshot, i have tried to apply triggers like as mentioned in screenshot. So let me know i am doing in correct way or not ?

Re: Help me on my issue

Brett_Bleess — Wed, 25 Mar 2020 19:35:03 GMT

Just adding a couple of Design Blog posts for you that may prove helpful in getting things sorted out in relation to what the partner suggested...

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

Here is the base URL for the Design Blog area in case you want to do some further searching on your own:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Hopefully this gets you the rest of the way with things or explains them such that you can sort out what you need to do in order to get things working for your use case.

Regards,
Brett