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

[#1]

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.

[#2]

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

[#3]

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>

[#4]

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]

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

[#6]

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.

[#7]

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]

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

[#9]

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]

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