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]

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.

[d_pranskus]

Hi Rikab,

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

Cheers, Darius.

[#4]

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.

[#5]

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;

[d_pranskus]

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

[#7]

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;

[#8]

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.

[d_pranskus]

Hi

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

[#10]

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

[#11]

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;