Number format

[riyazasma1009]

Hi All,

I have a field coming from excel which contains values such as given below:

A

100

2000

3000000

1029883

2000 should be represented as 2K, 3000000 should be represented as 3M and so on.

Is there any possible way to achieve the same in Qlikview?

Thanks,

Asma

[Anonymous]

see the attachment..

Properties -> Number -> Define The Symbols..

QV automatically does it as per values..

[Anonymous]

For a Chart you can set the Thousand and Million Symbol on the Number tab.

[sunny_talwar · Accepted solution]

May be this:

Dual(If(A < 1000000, A/1000 & 'K', If(A < 1000000000, A/1000000 & 'M', A/1000000000 & 'B')), A) as NewA

Dual(If(A < 1000, A, If(A < 1000000, A/1000 & 'K', If(A < 1000000000, A/1000000 & 'M', A/1000000000 & 'B'))), A) as NewA;

Update: Sample Attached

Table:

LOAD *,

  Dual(If(A < 1000, A, If(A < 1000000, Round(A/1000, 0.01) & 'K', If(A < 1000000000, Round(A/1000000, 0.01) & 'M', Round(A/1000000000, 0.01) & 'B'))), A) as NewA;

LOAD * Inline [

A

100

2000

3000000

1029883

];

[settu_periasamy]

Another One. May be. We can use the Same in the Expression Also. Just Try if(Sum(Data)>100000000....num(sum(Data)/100000000

Load Data,
if(Data > 100000000,num(Data/100000000, '#,##0B'),
  if(Data > 1000000,num(Data/1000000, '#,##0M'),
    if(Data > 1000,num(Data/1000,  '#,##0K'), 
    num(Data, '#,##0')))) as Data_Symbol;
Load * Inline [
Data
123
1234
12345
123456
1234567
12345678
123456789
1234567890
];

[riyazasma1009]

Hi Balraj,

Thanks for the quick reply!!

Under Properties->Number->Symbols, all the symbols are greyed out. I cannot enter any Symbol Values.