How to create the interval match and connect it to... - Page 3 - Qlik Community

Report Inappropriate Content · ‎2010-07-03

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.

Report Inappropriate Content · ‎2010-07-08

Hi Nick,

Many thanks for giving your valuable suggestion. Surely I will try it!

By the way I am still waiting for your help in the below post if you can remember.

http://community.qlik.com/forums/t/31066.aspx

Report Inappropriate Content · ‎2010-07-08

Hi Darius,

Could you please explain how these below script works for better understanding. Please explain it when you get free time.

Thanks in advance


AUTONUMBER(DATE(FLOOR(fsemp_date_from)) & '|' & DATE(FLOOR(fsemp_date_to)) & '|' & fscode, 'from_to_fscode') AS %ID,


LOAD
 %ID,
 fsstockistmap.fscode AS fscode,
 DATE(FLOOR(fsstockistmap_date_from)) AS date_from,
 DATE(FLOOR(fsstockistmap_date_to)) AS date_to
RESIDENT fsstockistmap;



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

d_pranskus · ‎2010-07-08

Hi

Function AUTONUMBER(Value, KeyString) returns unique numbers for each value of parameter anywhere in a script, and I use it to have a number instead of long composite key consisting of several fields. Second parameter is used if you want to have several autonumber sets. Also this approach is good to avoid synthetic keys

So if you use AUTONUMBER with the same KeyString value in several load statements, then if values for first parameters are the same, function AUTONUMBER returns the same result. And then you can use it as a key field to connect tables.

Of course it could be a problem, if you use AUTONUMBER and then store results of a table into qvd files. If you load data from qvd and then append some new data where are some calculated autonumber values, it will give you wrong result. So use it with caution.

So in your case I would like to have a key consisting of date_from, date_to and fs_code fields. I could just concatenate all three values, and have a key field. To have some smaller representation of a key field I concatenate them and get unique numbers for each unique combination of fields values.

Also I use FLOOR(date) because I saw that your date_from and date_to fields contains time part, so i just have a good practice get rid of this time part, just to avoid problems. And then convert the dates back to date format.

Hope it helps a little bit

Cheers, Darius

Report Inappropriate Content · ‎2010-07-09

Hi Darius,

First of I am really sorry If my question are very silly. But I promise you that If you help me out I am sure that I will advanced knowledge very soon.

As said I am able to understand very little but thanks for the effort though. I feel it will be better understandable if put some values in the below script and explain me.

<pre>AUTONUMBER(DATE(FLOOR(fsemp_date_from)) & '|' & DATE(FLOOR(fsemp_date_to)) & '|' & fscode, 'from_to_fscode') AS %ID

I am expecting some like this

<pre>AUTONUMBER(DATE(FLOOR(fsemp_date_from)) & '|' & DATE(FLOOR(fsemp_date_to)) & '|' & fscode, 'from_to_fscode') AS %ID

DATE(FLOOR(fsemp_date_from))=03/11/09
DATE(FLOOR(fsemp_date_to))=08/01/11
fscode=11KNE01

After concatenating it I will get some values like this as per my assumption. But not able to understand what this(from_to_fscode
) here.
So can you please explain something like this.

03/11/09|08/01/11|11KNE01

Also I am waiting for explanation for for below script

<pre>LOAD
 %ID,
 fsstockistmap.fscode AS fscode,
 DATE(FLOOR(fsstockistmap_date_from)) AS date_from,
 DATE(FLOOR(fsstockistmap_date_to)) AS date_to
RESIDENT fsstockistmap;

Especially this one:

Not able to understand what this does. Please explain this also.

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

d_pranskus · ‎2010-07-09

Hi

I will explain the process.

In your examle you have 3 fact tables with date intervals date_from and date_to, containing one common key field fscode. To associate these tables to date table you need common table (linktable) which contains all the values of date intervals altogether with fscode dimension. This linktable will be associated with each fact table and date table. To avoid synthetic keys you need one artificial key field per each fact table to connect it to linktable. So you concatenate date_from, date_to and fscode fields in each fact table and get this artificial key field.

Then you create linktable from each already loaded fact table (you use resident keyword to do that). You load id field, both date interval fields and fscode field and then load just distinct records in this table.

Now we have link table, which is connected to each fact table by id and to master dimension table by fscode. Now we need to connect the interval from linktable to calendar table. For that I use intervalmach, which creates a table containg date interval fields and date field in that interval.

After joining it to link table, I get CalendarDate field added to link table which is now connected to Calendar table.

To see what every command does, you should better look in manual, it is quite extensive.

Cheers, Darius

Report Inappropriate Content · ‎2010-07-19

Hi Darius,

First of all sorry for the very late response as I was on long leave. Though I am not able to understand what you technically I am able to understand the process. I feel I need to ask the doubts one by one then only I will be able understand and it will be easy for you also.

Let me start the first,


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

What you are doing it here. I mean which is the left table here. As per me I guess it is left table is calendertable and the right table is the link table. If I am right you are joining(left) the result of calendertable to the link table.

Report Inappropriate Content · ‎2010-07-21

Hi Darius,

Hope you are doing good. First of all sorry for the trouble again. I am facing some problem again which I couldn't solve it.

In my previous document after adding the few tables there are so many loosely coupled key has been formed which I am not sure how to avoid it.

I seriously need of your help to avoid the same. Have the attached the document and will be sending you the data source to you to work on.

Report Inappropriate Content · ‎2010-07-21

Hi Darius,

I have uploaded the DB here. Requesting you to work on it when you are free and please try to help me out.

Also requesting you to change the connection string there. Download the data source from below link.

http://www.sendspace.com/file/hhku3l

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

This is very urgent requirement and need your help badly!

d_pranskus · ‎2010-07-21

Hi

I am quite bussy these days. After trying to open your application I was not able to do so. It consummed all my memory and computer froze. Please send me qvw application with data removed and I will take a look.

Cheers

Darius

Report Inappropriate Content · ‎2010-07-22

Hi Darius,

Many thanks for your reply! It is indeed consuming lots of memory from my computer also.

I don't want to disturb you. Please do it only when you are free. This is not the complete data there are few more tables need to be added to this document. So may need your help later on also. Sorry for the trouble which I am causing it to you. But I am not doing intentionally as this is my first project and this is the most critical project of my organization. So requesting you to be patient if my questions are too silly!

With your help I am sure I will also become one of the expert one day.

As requested I have attached the document again without any day(While debugging loaded the document with 0 rows so that you can see the internal table view). Hope it will not consume much memory and document will opened without any issue.

Requesting you to do the required changes in my document so that I can get the rest of the tables from other technical team which has to be added to this document once this issue has been solved.

Advance thanks to you...

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