Qlik Community

QlikView Documents

Documents for QlikView related information.

Linking to two or more dates

MVP & Luminary
MVP & Luminary

Linking to two or more dates

This tutorial presents a script pattern for assigning time dimensions to multiple fact dates in a data model. It answers the commonly asked Forum question "how do I link to two dates"?

The pattern will demonstrate how to link all fact dates to a common calendar as well as using separate calendars for each fact date.

Labels (2)
Attachments
Comments
jumiprado
Contributor

I have this problem:

The MonthYear duplicate my registers, how can i fix this? I need to have one register per FACEND field.@Rob Wunderlinch rwunderlich

THANKS!

error.PNG

0 Likes
MVP & Luminary
MVP & Luminary

Juan,

I suggest you start a new thread for this question and attach a sample QVW if possible.

-Rob

0 Likes
sanjyotpatkar
Contributor III

Hi Rob

I have used the above example that you had pasted in the sample qvw file and created a canonical calendar to link to the Date Raised and Date Resolved fields in my extract as below. The sub routine for generating calendar is exactly the same pasted in your qvw file.

DateLink:
// TR Extract Date Raised
LOAD
[TR #]
,
DateRaised as Date // Fact Date
,'Created' as DateType // Fact Type
RESIDENT Resident_TR_Dump
;

// TR Extract Date Resolved

LOAD
[TR #]
,
DateResolved as Date
,'Resolved'
as DateType
RESIDENT Resident_TR_Dump
;

CALL CalendarFromField('Date', 'CommonCalendar', '');
CALL CalendarFromField('DateRaised', 'CreatedCalendar', 'Created ');
CALL CalendarFromField('DateResolved', 'ResolvedCalendar', 'Resolved ');

Besides the Created and Resolved tickets I need to calculate the Outstanding tickets.

Outstanding tickets are all tickets with Status ='Open'.

I have used the Weekstart for the X axis and use the following expression count(DISTINCT{<Status={'Open'},[Referred to CPS]={'Y'}>}[TR #]) . Essentially it needs to show all tickets that are having status ='Open' even if there is a resolved date for all successive weeks until the Status is changed to Closed as of date. so if a ticket has a Open status that was created in 2015 it needs to reflect in the weekstart of 13 Oct 2016.

Issue that I am facing is that ticket is reflected only in the week that it has been created and in the week that it has been resolved. It is not being considered in the missing weeks between  the 2 dates nor in subsequent weeks after the resolved date until today's date. Will you be able to assist me or guide me in the right direction?Capture.JPG

0 Likes
vadim_grab
Contributor

rwunderlich

Dear Rob.

I have a problem (link to different date) that I want to solve.
Can you place full script text here?

I have Data Model which built at General Table - "Facts Table". In  this table I used different  keys include Date (Floor(Date) as DateID). On DateID key I built Master calendar like Torben Seebach/Martin Didriksen

The Fastest Dynamic Calendar Script (Ever). I know that you also participated in the development.

In my Data  Model there is "Monthly Customer Plans Table" with fields MonthPlan, Customer_ID, Plan.

How I can linked Month (which generate by using DateID in Facts Table) in  Master Calendar   with MonthPlan in  Monthly Customer Plans Table? How can I use Master Calendar to see Customer Actuals and Plans both. !D__QlikView_test2.qvw 2017-01-04 17.25.50.png

Thanks a lot for any help!

0 Likes
MVP & Luminary
MVP & Luminary

Create a bridge (link table) the associates MonthPlan field with all DateID values for that month.

-Rob

0 Likes
ahaahaaha
Honored Contributor

It may, in addition tables "Sales" and "Mutual payments" associate artificially created composite key DenID-DogovorID?

(Может в дополнение таблицы "Продажи" и "Взаиморасчеты" связать искусственно созданным составным ключом ДеньID-ДоговорID?)

0 Likes
vadim_grab
Contributor

Andrey, thanks for your comment!

Sales Table and Mutual payments Table are Facts. I'm combine this tables in one - Facts ( by Outer Join )

Tried your advice, but declined (Why? below in the text)

D__QlikView_test2.qvw 2017-01-05 13.09.02.png

Tried to combine more - Sales / Mutual payments / Remains Tables in one Facts in order to get rid of  unnecessary synthetic keys. The result was only one Syn key (Партнер_ID & Договор_ID), but sales actuals (sum(Сумма))  calculated incorrect. Don't understand why,  it will my new task for understanding

Current data structures seems like screenshot higher.

Andrey, may be you can give more advices for optimization my data structures?

Next step will try use Rob advice rwunderlich - bridge (link table) the associates MonthPlan field with all DateID.

While not even imagine how I will   associates MonthPlan (Jan-2017) and Date  (01.01.2017) DateID (Floor(Date) As ДеньID).

Thanks a lot!

0 Likes
ahaahaaha
Honored Contributor

Do I understand correctly that the table "Monthly Customer Plans Table" should be added in the data model? If so, then the Master Calendar, you must add the field to match the format with the field MonthYear in the table "Monthly Customer Plans Table". In an extreme case, you should create a new field in the same Master Calendar and in the table "Monthly Customer Plans Table". And he does not have a field value format. This may be Jan2016, and the first day of the month (01.01.2016), etc.

(Я правильно понял, что таблица "Месячный план по работе с клиентами" должна быть добавлена в представленную модель данных? Если да, то в Мастер-календаре необходимо добавить поле, совпадающее по формату с полем МесяцГод в таблице "Месячный план по работе с клиентами".

В крайнем случае следует сформировать новые одинаковые поля и в Мастер-календаре, и в таблице "Месячный план по работе с клиентами". Причем сам формат поля значения не имеет. Это может быть и Янв2016, и первый день месяца (01.01.2016), и т.п.)

0 Likes
ahaahaaha
Honored Contributor

With regard to the synthetic key ДеньID-Договор_ID - I offered to create it "manually", avoiding the possibility of linking the tables platform QlikView.

Examples of such keys by creating a table of links, see Chapter 8, "Data Modeling Best Practices" book "QlikView 11 for Developers" authors Miguel García and Barry Harmsen.

Vadim, I think the discussion we abuse our hospitality Rob page.

(Что касается синтетического ключа ДеньID-Договор_ID  - я предлагал его создать "вручную", обходя возможности связывания таблиц платформой QlikView.

Примеры создания  таких ключей через таблицу ссылок приведены в Главе 8  "Data Modeling Best Practices"  книги "QlikView 11 for Developers" авторов Miguel García и Barry Harmsen.

Вадим, мне кажется, своим обсуждением мы злоупотребляем гостеприимством на странице Роба)

0 Likes
vadim_grab
Contributor

Andrey, you are correct understand. I added  "Monthly Customer Plans" Table at "Facts" Table (both  Tables have Date fields), used The Fastest Dynamic Calendar Script (Ever) to generated MasterCalendar based on Date fields. At first sight works correctly, but my testing continues...

******************************************************************************************

(Андрей, Вы совершено правильно все поняли. С одним уточнением, я решил добавить Таблицу Клиентских Месячных не отдельной таблицей, а как часть таблицы Факты (объединив две таблицы Concatenate). Обе таблицы имеют поля Дата . Используя алгоритм/скрипт Мастер Календаря (Torben Seebach), который автоматически генерирует календарь основываясь на поле Дата, Календарь работает корректно и отражает одновременно и Планы и Факты. При первом приближении все работает, но я не останавливаю поиски возможных ошибок...

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2014-02-07 02:44 PM
Updated by: