topic Re: How to create dates when they don't exist in QlikView

How to create dates when they don't exist

rarora12 — Wed, 02 Mar 2016 19:17:21 GMT

I am using a master calendar to have the user select dates.

The input from the user is used to look between ranges of dates, start and end.

I am using an iteration to generate dates from range start to range end.

The while loop with the iteration runs for 20 mins and has 100 million rows.

I cannot use interval match as the Date is entered as an input and doesn't exist in the database.

Any ideas ?

Thanks for your input.

Rick

Re: How to create dates when they don't exist

swuehl — Wed, 02 Mar 2016 19:49:15 GMT

Not sure if I understand what you are trying to achieve.

Could you post a small sample QVW with some mock up data or the script you are using?

Re: How to create dates when they don't exist

mohamed_ahid — Wed, 02 Mar 2016 20:06:02 GMT

using a master calendar is generally achieved by making a connection (link) between you fact table (using facte date ) to your master calendare date .

tomake selection between to dates you can do that by using set analysis ..

Re: How to create dates when they don't exist

rarora12 — Wed, 02 Mar 2016 20:27:06 GMT

So I am using an input box and filters related to master calendar so there are 2 ways to enter dates

The calendar is connected to the data via keys and analysis date

The idea to use a calendar was to NOT use set analysis - but in this case I believe I have to use both

Any suggestions?

Thanks

Rick

Re: How to create dates when they don't exist

Anonymous — Wed, 02 Mar 2016 20:31:57 GMT

Hello Rick,

I typically use some form of a Master Calendar script when building out my date functions. You are welcome to use this version that allows you to hard code in a start and end date for the range of your calendar. If you want to take it to the next level and make it more dynamic, you can change the Let statements below for the vMinDate and vMaxDate variables to any formula you like that results in a date value or numeric value that represents a date.

Once you have this code added to your load script, you can join the Master Calendar into your existing data simply by aliasing a field in that dataset as either "Date" (for Date formatted fields) or "DateKey" (for Numeric Date fields).

I hope that this helps you, please let me know if you have additional questions, I would be happy to help further.

-Mark

// ==========================================================

// Master Calendar Generation Script

// ==========================================================

LET vMinDate = Num(MakeDate(2016,1,1));

LET vMaxDate = Num(MakeDate(2016,12,31));

LET vNumberOfDays = Floor($(vMaxDate)) - Floor($(vMinDate)) + 1;

Calendar:

LOAD Distinct

Num(Floor(Date)) as DateKey,

Date(Floor(Date)) as Date,

Month(Date) as Month,

Date(MonthStart(Date), 'MMM YYYY') as [Month and Year],

Day(Date) as Day,

Year(YearStart(Date)) AS Year,

'Q'& Ceil(Month(Date)/3) AS Quarter,

Week(Date) as Week;

LOAD

Date(Num($(vMaxDate)) - RecNo() + 1) as Date

AutoGenerate($(vNumberOfDays));

Re: How to create dates when they don't exist

rarora12 — Wed, 02 Mar 2016 21:35:08 GMT

Thanks for the response and the script. So I am building a master calendar with a min and max date and I am populating the Supervision table with range of dates also with an iteration and while loop.

Then I am joining the calendar with the table with ranges using a common field name.. the link table

Appreciate the feedback and it seems that populating dense Dat in 2 places is needed.

The script runs for about 20 mins as it trying to build a calendar from 1985 to future year 2099.

Wish there was a way to tune the code for quicker reload..

Thanks to all and I will keep you updated.

Rick

Re: How to create dates when they don't exist

megabyte23 — Thu, 10 May 2018 13:43:36 GMT

I'm using this, great script. Thank you!