topic Re: Difference between (num and num#) and (date & date#) in QlikView

Difference between (num and num#) and (date & date#)

dsdeepak284 — Wed, 25 Nov 2020 16:16:04 GMT

Please tell me difference between (num and num#) and (date & date#), I request you to please reply with some examples, if you can.

Re: Difference between (num and num#) and (date & date#)

ali_hijazi — Thu, 01 Feb 2018 13:15:39 GMT

let's say, you have a result number 5555.1234567 and your user wants to see only till two decimals. Here num() helps you - Num(5555.1234567, '##0.##') -> 5555.12 and the backgound number remains as it is.

You get a sting which is aaa12234, and your user asks to get the number part only. You can use num#() -

=Num(Num#(aaa12234, 'aaa#####')) -> 12234

Re: Difference between (num and num#) and (date & date#)

dsdeepak284 — Thu, 01 Feb 2018 13:22:27 GMT

Thanks

Re: Difference between (num and num#) and (date & date#)

Miguel_Angel_Baeyens — Thu, 01 Feb 2018 13:24:34 GMT

Num() and Date() functions take a number as first and mandatory parameter, and they return a representation of such number as specified in the second parameter, or by default (e.g.: how many decimal places, how the negative values should be displayed, in the case of Num() and putting the month before the day or viceversa, in the case of Date()).

Date(43132) will return 01.02.2018 (using Swiss default locale settings)

Num(000002) will return 2

Num#() and Date#() functions take either a number or a literal which needs to first be interpreted into a number to be meaningful, so the result can be later used on a numeric function (such Num() or Date(), but also others).

For example, the number 20180201142000 could be 20 trillion, 180 billion, etc. or a numeric representation of a date: year, month, day, hour, minute, second. If the latter, however, Date() will not return 01.02.2018, but a date much farther in the future, so in order to instruct QlikView to "understand" that number as numeric representation with the format YYYYMMDDhhmmss you can use Date#()

Date(Date#(2018020114200, 'YYYYMMDDhhmmss')) will return 01.02.2018

Check https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/InterpretationFunctions/interpreta… for all possible parameters and further descriptions of these functions.

Re: Difference between (num and num#) and (date & date#)

sunny_talwar — Thu, 01 Feb 2018 13:28:13 GMT

The function without the hash (#) are format function. Basically help you assign a particular format to your field. The function with hash are interpretation function. Basically help QlikView understand if a particular field is a date (Date#()) or number (Num#()) because it is unable to understand it as a date. For example, your date is set as YYYY-MM-DD at the start of the script (environmental variable), but one of your database have date set as DD-MM-YY. Now QlikView may or may not understand this, but you can use Date#() to help QlikView understand the format.

Date(Date#(DateField, 'DD-MM-YY')) as DateField

Re: Difference between (num and num#) and (date & date#)

Peter_Cammaert — Thu, 01 Feb 2018 13:48:18 GMT

If you keep in mind that QlikView always tries to store both numerical values and dates as dual values with both a numerical (binary) respresentation and a string (text) representation, then the following may make things more clear:

Num#( ) is a function that converts a string representation into a real binary number and probably stores it as a dual into either a variable or a new field. If you specify a format string, that will become the text representation. Otherwise Num#() will use the default format string (see variables DecimalSep-ThousandSep).

Num#() = an interpretation function that interpretes data that goes into QlikView.

Num( ) on the other hand takes an existing dual value (from a field or a variable, or even a string which could be considered as a dual with a derived number representation after implicit conversion) and tries to format the binary value part into an ad-hoc text value according to the format string you specify as second parameter (or using the default DecimalSep-ThousandSep specification if you don't specify one).

Num() = a formatting function that formats internal data that is sent out from QlikView.

For examples, see:

Num# ‒ QlikView‌ (expand Examples and Results at the bottom)

Num ‒ QlikView (expand Examples and Results at the bottom)

Date() and Date#() do the same but now with date values which have a wider range of text representations that include names and punctuation characters.

For examples, see:

Date# ‒ QlikView (expand Examples and Results at the bottom)

Date ‒ QlikView (expand Examples and Results at the bottom)

Best,

Peter