Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to create the interval match and connect it to the master calender

Hi all,

There are so many date range is there in my application I don't have any idea how can i use this in my document. Major problem for me is to connect the master calender with the date range(from and to date). There are so many date range field is there in many of the tables of my document. So I confused on if i select the date or range of date how it will fall in this interval. Also all the from and to date in the tables are different.

So as to created the month,year and other dates field I have generated the master calender to date selection. But don't have any idea on how to connect this master calender with the date range and avoiding the synthetic key which may arise here.

Have attached the same document which I am working on. Hope some one can work on it to give me start.

49 Replies
Not applicable
Author

This is simplest approach to do this:


left join (pooldetail)
intervalmatch(CalendarDate)
load pool_from_date
,pool_to_date
resident pooldetail;


left join (callindex)
intervalmatch(CalendarDate)
load callindex_date_from
,callindex_date_to
resident callindex;

left join (fsemployee)
intervalmatch(CalendarDate)
load fsemp_date_from
,fsemp_date_to
resident fsemployee;

left join (fsstockistmap)
intervalmatch(CalendarDate)
load fsstockistmap_date_from
,fsstockistmap_date_to
resident fsstockistmap;


But it will duplicate records in joint table. You can create separate interval table for from and to dates with distinction for each table and do the same intervalMatch with the individual interval tables. You can see details of this approach here http://community.qlik.com/forums/t/23510.aspx

Not applicable
Author

Hi Nick,

Many thanks for your help. I am not able to understand the following statement. Could you please work on it for any one of the temple rest I will do it please. I had gone through the post which you have mentioned but not able to understand what to do. Hope you will work it for me.


Nick Bor wrote:
But it will duplicate records in joint table. You can create separate interval table for from and to dates with distinction for each table and do the same intervalMatch with the individual interval tables. You can see details of this approach here http://community.qlik.com/forums/t/23510.aspx<div></div>


Not applicable
Author

Hi Nick,

I have added your script in my document and as said my you there synthetic key got arises and there has been performance issue also. I am not sure whether I did the same thing as said by you. Requesting you to look into and let me know the solution please.

d_pranskus
Partner - Creator III
Partner - Creator III

Hi Rikab

The solution is to use link table approach. For that you should create ID's (representing all common dimensions) in every table you want to associate, and then load these ID's tpgether with common dimenisons into link table. Then you should associate fields in link table with master tables and calendar table as well.

Please see attached example. I just extracted tree tables from your file and created link table. Then I created calendar and associated it with a link table using interval match.

Cheers, Darius

Not applicable
Author

Hi Darius,

Many thanks for your quick help. I will definitely check the solution provided by you and will let you know whether the particular requirement suits me or not.

Not applicable
Author

Hi Darius,

Can you please work on my data itself and do the required changes without removing the master calender which I created. I am checking it how it can be added with complete data as you have did the changes only in the required table. When I add all the tables then only I will get to know. So asking you to do so.

d_pranskus
Partner - Creator III
Partner - Creator III

Hi, Rikab

Actually I need a datasource (from which you are loading data) for that. It will take a lot of time for me to prepare the datasource myself. Could you somehow give me it?

Cheers, Darius

Not applicable
Author

Hi Darius,

Can I export the data in to excel after scrambling the data. In that I hope you should be in a position to work on it.

Please do reply!

d_pranskus
Partner - Creator III
Partner - Creator III

Hi Rikab,

Please export the data into Excel and then adjust script to load that data. Then send this data with QV application to me. I will try to modify the script and find a solution.

Cheers, Darius