topic Re: Show the records which contains a particular date range in QlikView

Show the records which contains a particular date range

Thu, 15 Sep 2016 20:34:47 GMT

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

Re: Show the records which contains a particular date range

swuehl — Thu, 15 Sep 2016 20:42:47 GMT

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.

Re: Show the records which contains a particular date range

Fri, 16 Sep 2016 16:42:48 GMT

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

Re: Show the records which contains a particular date range

Fri, 16 Sep 2016 17:40:44 GMT

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

Re: Show the records which contains a particular date range

swuehl — Fri, 16 Sep 2016 17:56:21 GMT

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.

Re: Show the records which contains a particular date range

Fri, 16 Sep 2016 18:08:26 GMT

Thank you Stefan. This answers my question.