Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

QlikView Date fields

cancel
Showing results for 
Search instead for 
Did you mean: 
hic
Former Employee
Former Employee

QlikView Date fields

Last Update:

May 31, 2012 3:29:49 AM

Updated By:

hic

Created date:

May 31, 2012 3:29:49 AM

Attachments

QlikView has an intelligent algorithm to recognize dates independently of which region you are in. In most cases, you will have no problems loading them. It just works and you do not need to think about it. However, in some cases dates are not properly recognized and then you need to add some code in the script to make it work.

HIC

Comments
zturaiki
Contributor III
Contributor III

thanks for the document , we need more like this ,,

but really i want to ask you Qlikview m not support the Arabic/Hijri calender ...

how to converto from Gregorian calendar to other ?!

thanks

0 Likes
mellerbeck
Creator II
Creator II

Please yes, more like this!

0 Likes
Not applicable

Dear Henric

TIP5: always use the numeric value in variables

Fields are dual, but variables are not. This means that whenever you want to store a date in a variable and use it later for e.g. a numeric comparison in a where clause, it is easier if the variable is numeric instead of a string containing a date format

My question to you is: if exactly fields are always dual and, as you mentioned in the Data types paragraph

Numerical functions always use the numeric part of the dual field and string functions always use the string part.

What is then the added value of a variable for calculation purposes? I can't grasp this.

Inded, Thank you very much for the whole document, it is really helpful!

0 Likes
hic
Former Employee
Former Employee

Let's say that you want to use a date variable in a where clause:

   ... where OrderDate <= $(vCutOffDate)

This will not work for a textual variable. But it will for a numeric variable.

If I elaborate further: If you have a textual variable, the above variable expansion might expand to

  ... where OrderDate <= 2011-12-01

which in turn will mean that QlikView will calculate 2011 minus 12 minus 1 and compare this with the OrderDate which is roughly 40000 ...

If you instead use single quotes

  ... where OrderDate <= '$(vCutOffDate)'

then it might work - but only if the date format is recognized by QlikView.

It is very easy to make errors in this area - I have made most of them - and using numerical variables is a good way to avoid errors.

HIC

preminqlik
Specialist II
Specialist II

shall we use like this sir ?

where num#(orderdate)<=num#($(vCutOffDate))

0 Likes
hic
Former Employee
Former Employee

First - if the field orderdate already has been interpreted and is stored as a dual (i.e. it has numeric value), then the first num#()-function is redundant.

Secondly, if the variable vCutOffDate is a text (e.g. 10/12/2011), then you need single quotes around it.

In other words:  orderdate <= date#('$(vCutOffDate'))  will work fine.

HIC

preminqlik
Specialist II
Specialist II

Got you sir...Many thanks

0 Likes
Not applicable

Thank you very much .

but if you can help me in this (Is there anyway to build up Arabic calender (Hijri). it's come like (1430/04/12).)

0 Likes
hic
Former Employee
Former Employee

Traditionally the beginning of the Hijri month is based on actual witnessing of the moon crescent in Mekka, so it not so straightforward to create a calendar. I would suggest that you create your own calendar as an

Excel file or a text file. See below for a template (but make it complete so that all dates are included).

Further, if you google Hijri converter, you will find several web sites that you can use as input when creating this Excel sheet.

HIC

GregorianDate HijriYear HijriMonth HijriDay
2013-01-011434Safar19
2013-02-011434Raby` al-awal20
2013-03-011434Raby` al-THaany19
2013-04-011434Jumaada al-awal20
2013-05-011434Jumaada al-THaany21
2013-06-011434Rajab22
2013-07-011434SHa`baan22
2013-08-011434RamaDHaan24
2013-09-011434SHawwal25
2013-10-011434Thw al-Qi`dah25
2013-11-011434Thw al-Hijjah27
2013-12-011435MuHarram28
Not applicable

thank you hic

0 Likes
Version history
Last update:
‎2012-05-31 03:29 AM
Updated by:
Former Employee