topic Re: creating date dimension for every 15 mins in QlikView

creating date dimension for every 15 mins

Wed, 25 Nov 2020 16:16:04 GMT

I have scenario

I need to create date dimension which will populate records for every 15 mins.

Please suggest me the code

Regards

Thiru

Re: creating date dimension for every 15 mins

PrashantSangle — Wed, 08 Mar 2017 06:26:17 GMT

Hi,

can you explain with sample data??

Regards,

Prashant Sangle

Re: creating date dimension for every 15 mins

rahulpawarb — Wed, 08 Mar 2017 06:48:40 GMT

Hello Thiru,

Sorry to say but your query sounds ambiguous to me. Do you mean to say, you need a script to product a Time dimension table with an attribute with 15 mins level values or a script to implement incremental load?

Could you please shed more light on it? This will help us to provide you expected response.

Regards!

Rahul

Re: creating date dimension for every 15 mins

Wed, 08 Mar 2017 06:52:52 GMT

Hi Rahul,

Yes you are right.

I need to create date dimension with 15 minutes interval

i.e 2000-01-01 00:00

2000-01-01 00:15

2000-01-01 00:30

2000-01-01 00:45

2000-01-01 00:60

.....

2017-03-06 24:00

Like that.

Regards

Thiru

Re: creating date dimension for every 15 mins

Wed, 08 Mar 2017 06:54:37 GMT

Hi Prashant,

I need to create date dimension with 15 minutes interval

i.e 2000-01-01 00:00

2000-01-01 00:15

2000-01-01 00:30

2000-01-01 00:45

2000-01-01 00:60

.....

2017-03-06 24:00

Like that.Is this fine or can i attach sample application which is currently day wise

Regards

Thiru

Re: creating date dimension for every 15 mins

jonathandienst — Wed, 08 Mar 2017 06:57:42 GMT

This will round the time to the nearest 15 minutes

=Time(Round(TimeField, 15/24/60))

You should explain your requirements in more detail for more specific help.

Re: creating date dimension for every 15 mins

jonathandienst — Wed, 08 Mar 2017 07:07:43 GMT

If you are running 15 minute intervals since 2000, I recommend you split the time and date into 2 different fields.

Re: creating date dimension for every 15 mins

jonathandienst — Wed, 08 Mar 2017 07:10:15 GMT

Generate the dates and times like this

Let vMaxDate = ...;

Let vMinDate = ...;

Let vPer = 15/ (24 * 60);

Cal:

Load

Time(Frac((RowNo() - 1) * $(vPer))) as Time

Date(Floor((RowNo() - 1) * $(vPer)) as Date

AutoGenerate Ceil(('$(vMaxDate)' - '$(vMinDate)') / $(vPer));

You will need to set the values of vMinDate and vMaxDate per your requirements.

Re: creating date dimension for every 15 mins

tresB — Wed, 08 Mar 2017 07:14:44 GMT

Try like:

let vStartDate=Num(MakeDate(2000,1,1));

Load

Timestamp($(vStartDate)+(1/24/60*15)*(IterNo()-1))as DateTime

AutoGenerate 1 while $(vStartDate)+(1/24/60*15)*(IterNo()-1) <=Today();

Re: creating date dimension for every 15 mins

Wed, 08 Mar 2017 07:51:52 GMT

Hi Jonathan,

Please find the attached application and modified the code in calender.

Thanks

Thiru