QlikView Date fields

[hic]

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

[zturaiki]

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

[mellerbeck]

Please yes, more like this!

[#4]

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!

[hic]

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]

shall we use like this sir ?

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

[hic]

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]

Got you sir...Many thanks

[#9]

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).)

[hic]

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-01	1434	Safar	19
2013-02-01	1434	Raby` al-awal	20
2013-03-01	1434	Raby` al-THaany	19
2013-04-01	1434	Jumaada al-awal	20
2013-05-01	1434	Jumaada al-THaany	21
2013-06-01	1434	Rajab	22
2013-07-01	1434	SHa`baan	22
2013-08-01	1434	RamaDHaan	24
2013-09-01	1434	SHawwal	25
2013-10-01	1434	Thw al-Qi`dah	25
2013-11-01	1434	Thw al-Hijjah	27
2013-12-01	1435	MuHarram	28

[#11]

thank you hic