topic How to create the interval match and connect it to the master calender in QlikView

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

Sat, 03 Jul 2010 08:22:32 GMT

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.

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

Sat, 03 Jul 2010 14:47:07 GMT

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

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

Mon, 05 Jul 2010 06:11:27 GMT

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>

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

Mon, 05 Jul 2010 06:51:36 GMT

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.

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

d_pranskus — Mon, 05 Jul 2010 13:18:47 GMT

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

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

Tue, 06 Jul 2010 06:07:31 GMT

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.

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

Tue, 06 Jul 2010 08:16:14 GMT

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.

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

d_pranskus — Tue, 06 Jul 2010 10:19:20 GMT

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

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

Wed, 07 Jul 2010 05:24:56 GMT

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!

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

d_pranskus — Wed, 07 Jul 2010 07:06:05 GMT

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

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

Wed, 07 Jul 2010 13:38:13 GMT

Hi Darius,

Thanks for your interest to help me out. As requested I have uploaded the database as you wanted to work on it. I hope the database contains everything that you are looking for. Please let me know if anything is missing as have imported the data in to the uploaded database after scrambling the data in qlikview.

Link to download the data source: http://www.sendspace.com/file/x9rqv1

By the way for your information attached data source is MS SQL Server database.

Requesting you work on it and provide me the solution which I am looking for.

I am very eagerly waiting for the solution which you are going to provide.

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

d_pranskus — Wed, 07 Jul 2010 14:54:02 GMT

Hi Rikab,

This is a solution. Please change connection string and reload data.

Cheers, Darius.

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

Wed, 07 Jul 2010 15:19:44 GMT

Hi Darius,

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

Many thanks for your quick response!

I am still wondering how quickly you were able to understand the data model which I am working without having any previous knowledge about it. By the way I have to create few reports and dashboards then only I can tell you the outcome as I am still not able to understand the logic which you have used here. Probably it may take little longer time for me to understand what you did here to make it work properly.

I will check everything that you did here and will also verify the values then will get back to you if I have any doubts and also let you the outcome after that.

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

Thu, 08 Jul 2010 07:31:28 GMT

Hi Darius,

Once the data has been loaded after changing the connection string I am getting this error. Let me what may be the cause of this error. Is it related to hardware or some other issue is there.

Please check at your end and let me know the outcome.

Also just for your information I have added changed the script which you have forgot to do it. But this is not the reason for that error as I got the error before adding this itself.

Hope to get receive quick reply from your end.


fsemployee:
LOAD
 AUTONUMBER(DATE(FLOOR(fsemp_date_from)) & '|' & DATE(FLOOR(fsemp_date_to)) & '|' & fscode, 'from_to_fscode') AS %ID,
 empcode,
 fscode AS fsemployee.fscode,
 trancode,
 fsemp_date_from,
 fsemp_date_to;
SQL select c_Fs_code fscode,
c_emp_code empcode,
ltrim(rtrim(c_Fs_code)) + ltrim(rtrim(c_emp_code)) trancode,
d_date_from fsemp_date_from,
isnull(d_date_to,dateadd(month,6,getdate())) fsemp_date_to
from tbl_fs_emp_rel fsemp;
CONCATENATE (tmp)
LOAD
 %ID,
 fsemployee.fscode AS fscode,
 DATE(FLOOR(fsemp_date_from)) AS date_from,
 DATE(FLOOR(fsemp_date_to)) AS date_to
RESIDENT fsemployee;

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

d_pranskus — Thu, 08 Jul 2010 08:08:44 GMT

Hi, Rikab

I think this is related to that fact, that you have not enough RAM for that operation. Intervalmatch commands usualy generates a lot of records, because they associate every interval with every date which is in that interval.

As you can see it already generated some 1,6 M records.

Try the same script on a computer with higher amount of memory.

Cheers, Darius

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

Thu, 08 Jul 2010 08:45:45 GMT

Hi Darius,

Many thanks for your time again!

Surely I will check in check other computer if it has higher memory than mine . By the way for you kind information if I am removing below script there is no problem at all. I am able to load the data without getting any error message.

Is there is any other way to avoid the interval match in my document and it should work as expected.

I don't know the purpose of interval match. But giving you my suggestions! As per my my understanding I think we load the fscode,%ID,from_date and to_date in to the link table for associating every interval. If it possible to provide these details by writing SQL and loading in to the linktable to avoid this problem. Please correct me if I am wrong.

Think about it and let me know!


LEFT JOIN (linktable)
INTERVALMATCH (CalendarDate)
LOAD date_from, date_to RESIDENT linktable;

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

Thu, 08 Jul 2010 10:35:54 GMT

Hi Darius,

Strange thing happening here. Even with that interval script which I removed previously and with the same data when I reloaded I am not getting any such error again. All the data has been loaded successfully without any problem. For me this is not the first time it is happening, it has happened many times for me previously also. I never got any reason for this.

By the can you please answer me rest of the questions which I asked to you in my previous post.

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

d_pranskus — Thu, 08 Jul 2010 11:53:58 GMT

It seems that QV acts sometimes unstably.

Surely, You can use sql to join interval with a value, but in Qlikview there is no way to do that besides intervalmatch command.

the result of the command is a table wich consists of from and to fields and value field as well. So in your example it will contain date_from, date_to and CalendarDate fields.

Joining this to linktable allows to avoid synthetic key of date_from and date_to fields.

Cheers, Darius

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

Thu, 08 Jul 2010 13:22:46 GMT

Hi Darius,

You have done a amazing work. I am seeing some visibility that it works as per my requirement. Really a wonderful stuff. But still I need some time to do some more work around and cross verify whether everything is working or not.

Few things I could not understand. Will be much thankful to you if you can spend some time for this and give me the answer as next time don't want to be dependent on you.

Will let you know my doubts in my next post

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

Thu, 08 Jul 2010 13:44:21 GMT

To avoid the memory problem with intervalMatch, instead of this:

LEFT JOIN (linktable)
INTERVALMATCH (Date)
LOAD date_from, date_to RESIDENT linktable;

use this:


left join (linktable)
Load
 *,
 date(date_from + iterno() - 1) as Date
resident linktable
while date_from + iterno() - 1 <= date_to;