Qlik Community

Ask a Question

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
Henric_Cronström

Numbers and dates are a never-ending source of concern when building any BI solution.

But in QlikView, there is always a way to solve a problem… First, QlikView can interpret and format numbers and dates using functions in the script:

Number Format in Script.png

Alternatively, it can format them in the user interface, where each object, dimension or expression potentially can have its own number formatting:

Number Format Dialog small.png

The common denominator for interpretation and formatting in QlikView is the Format Code. QlikView uses it as a parameter in many functions, and you can find it in the Format Pattern on the Number tab in the properties dialog.

Some rules of thumb around the format codes:

  • Unrecognized characters in the format code can prevent QlikView from interpreting a number.
  • The codes are case sensitive. For instance, M means month, whereas m means minutes. Some symbols can be written in either upper or lower case, e.g. AM/PM (or am/pm) and XIV (or xiv). For these, the format code must correspond to what you want: TT for AM/PM; tt for am/pm; (ROM) for XIV and (rom) for xiv.
  • With the format code you can force a rounded display, e.g., by specifying two decimals on a multi-decimal number or by specifying year and month, but not day, when displaying a date. Such a rounding will only change the display and not the underlying number. To change the numeric value, you need to use a rounding function, e.g., Round(), Ceil() or MonthStart().

You can specify Integers to have leading zeros. You can also specify them as binary, octal, decimal and hexadecimal numbers. In fact, you can use any radix from 2 to 36. You can also format them as Roman numerals.

    Num( Number, '000000' ) as Number // Always with at least 6 digits. Leading zeros!
    Num( Number, '(R36)' ) as Number // Displayed in Radix 36 (like hex, but with 36 symbols)
    Num( Number, '(ROM)' ) as Number // Upper case Roman numerals

See picture below for the different cases. Each column denotes a specific format code. The rows are examples of input numbers and how QlikView will format them.

Integer formatting.png


Float numbers are similar. If you need QlikView to interpret a number that has a decimal symbol different from the one in the environment variables, be sure to use the third and fourth parameters of the Num#() function to specify decimal character and thousand separator. A correct format code is not enough.

          Num#( Number, '0,0', ',' , '.' ) as Num // Number with decimal comma and point as separator


Float formatting.png

QlikView interprets the integer part of a date serial number as a date – the number of days from Dec 30, 1899. Date formats are different from country to country so you sometimes need to specify which format you want. Note that you can specify weekday also..

          Date( MonthStart( date ), 'YYYY MMM' ) as YearMonth


Date formatting.png

QlikView interprets the fractional part of a date serial number as time of day. This can be specified in hours and minutes, etc. Note that the TT symbol denotes AM/PM. If this is not used, QlikView will assume 24-hour notation.

          Time( Ceil( Time, 1/24/4 ), 'hh:mm' ) as Time // Time rounded downwards to nearest 15 min


Time formatting.png

I recommend that you use interpretation, rounding, and formatting functions in the script to transform data into a form that you want.

HIC

Further reading related to this topic:

Data Types in QlikView

Get the Dates Right

QlikView Date fields

25 Comments
nivellen11
Contributor III
Contributor III

Usefull tips but i still dont kwon how to convert 19 characters string ( first char is a 0 or - for negative value) to number:

Example:

0000000000004964,66

-000000000146021,00

0000000000022940,97

-000000000001000,00

-000000000000013,00

Expected output:

    4964,66

-146021,00

   22940,97

   -1000,00

       -13,00

thanks for help.

0 Likes
3,670 Views
Henric_Cronström

QlikView cannot format numbers with more than 14 digits. And it cannot automatically interpret strings that long either.

But there is a work-around: The function Evaluate() can be used for an evaluation that goes beyond normal number interpretation.Try the following:

Load Evaluate(Number) as Number inline

[Number

0000000000004964,66

-000000000146021,00

0000000000022940,97

-000000000001000,00

-000000000000013,00

pi()

5*6];

HIC

3,670 Views
nivellen11
Contributor III
Contributor III

Yeah, THX for help. i use somthing like this and it works :

LOAD Evaluate(Left(@3,16))+if(Left(@3,1)='-',-(num#(Right(@3,2))/100),(num#(Right(@3,2))/100)) as ny_number

0 Likes
3,670 Views
Clever_Anjos
Employee
Employee

@hic, is there any format_code to show week numbers?

I couldn´t find the complete list of tokens and format inside QlikView Documentation

3,670 Views
Henric_Cronström

cleveranjos

No, unfortunately there is not. You will have to create it by using the week function and string concatenation.

HIC

3,670 Views
gautik92
Specialist III
Specialist III

very helpful

0 Likes
3,670 Views