How the Date fill will be considered when it is in range(From date and To date)

[#1]

Hi All,

We are facing major issue in one our application. The problem is with the Date field and there is no specific table in the database which can give the complete date information, means we can't get the complete date from one table itself. In all the tables Date field are in From and To date range.

In this case how which field I need to consider to create Day,Month or any other list box of date field.

For example:

If the user selects some specific date range then it should give the correct value as shown below in the comments.

~~~~~~~~~~~~~~~~~~~~~

FSMST AREA (Area where FS is working)

A X

B Y

C Z

D M

FSMST AREA FROM TO (Time period when FS was working in this area)

B X 01/01/10 31/03/10 // (This is should get selected if the selection is 15/03/10)

A X 01/04/10 30/07/10

B Y 01/04/10 31/03/11 // (This is should get selected if the selection is 01/05/10)

C Z 01/01/10 30/04/10

D Z 01/05/10 31/12/10

~~~~~~~~~~~~~~~~~~~~~~~

Please tell me what is the solution for this? How it can be achieved?

Only after doing this rest of the things can be done. So some one please help me out.

If my query is not understandable then please let me know the same.

[#2]

It sounds like what you are looking for is the classic "Master Calender"

see this qvw which Jason Long has provided or if you have access to the training examples, i am sure it includes one.

http://community.qlik.com/media/p/113268.aspx

[#3]

Hi,

Thanks a ton it was really helpful. With that I was able to create the master calender. Now the worry is how can i connect this master calender with my existing tables which is having the dates in from and to range. This is were the real problem is!

Do you have any idea how to do the same. If you have used this master calender in any of your documents which has from and to date range then you must be definitely knowing this. Please check and help me out.

[#4]

You are correct, there is a really possibilty to generate synthetic keys on this on but it depends on how you want to be able to interrogate the data.

I see 3 possibilites

1. In the load script you have a second entry for [From Date] but alias it as [Date]
2. In the load script you have a second entry for [To Date] but alias it as [Date]
3. You prefix all the fields in the master calendar with "From", create a 2nd master calender and prefixed with "To" giving you fields like From Month, To Month, From Year, To Year that you can refence in date sliders/calender pickers etc

What you can't really do is implement both 1& 2 at the same time as you would create a synth key

[#5]

Hi CowBoy,

Many thanks for your help. But my problem still not resolved. With little more help of yours I am sure that it can be solved.

1. In the load script you have a second entry for [From Date] but alias it as [Date]
2. In the load script you have a second entry for [To Date] but alias it as [Date]

-->

If I understood you correctly then this is what you wants me to do.

In all the load script wherever [From Date] is there I need to rename it [Date] and wherever [To Date] is there i need to rename it as [Date]. But can do any one at a time as it will create synthetic key.

You prefix all the fields in the master calendar with "From", create a 2nd master calender and prefixed with "To" giving you fields like From Month, To Month, From Year, To Year that you can refence in date sliders/calender pickers etc

What you can't really do is implement both 1& 2 at the same time as you would create a synth key

--> If I am renaming it as From in the first master calender and To in the second. Hope it will be connected to the loaded date. If the name itself is different how the synthetic key will happen.

Please help me out to come out of this as we really got struck up.

[#6]

Hi,

Please help me out if you are free as it is really urgent.