Show the records which contains a particular date range

[#1]

Hi All,

I need to show the records which contains the particular date range.

I have a table as follows:

Notice No	Leader Name	Model Year	Date 1	Date 2	Date 3	Date 4

I want to show all the records if I select a date range from calendar scroll and clicking the button. Even though the date range contains any date from the above 4 date fields it should show that row.

Thank You,

Abhijit

[swuehl · Accepted solution]

Create a key in your table load using

Table1:

LOAD

    Recno() as RecID,

     NoticeNo,

     LeaderName,

     ModelYear,

     Date1,

     Date2,

     Date3,

     Date4

FROM ...;

//Then create a LinkTable:

LinkTable:

CROSSTABLE (DateField, Date)

LOAD RecID, Date1,Date2,Date3,Date4 RESIDENT Table1;

Now you can select your range from Date field or link this field to a master calendar.

[#3]

This works out well. Thank you Stefan. I didn't give try with Master Calendar. I would love to know about any specific approach while working with Date fields to get much deeper insights.

Abhijit

[#4]

Hi Stefan,

What would be the performance of the application when it will go live. I have 47,000 rows an excel file. I have 8 attributes which are date type. So technically Cross Table will convert is as 47000*8 rows. Will this impact a performance? If yes what would be a best approach to tackle this?

Abhijit

[swuehl]

I don't see a big performance penalty in your case.

Also keep in mind that you have now 8 times the number of rows, but only 3 instead of 9 fields, and aggregated number of distinct values (i.e. number of bits in your bit stuffed pointer) might be even unproportional smaller.

Just run some tests.

[#6]

Thank you Stefan. This answers my question.