Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
jeevays7
Partner
Partner

Format issue with percent symbol

Hi All,

I have two excel file with some data type changes.

Excel 1:

The amount value has % symbol, while reading in qlikview in converts to number. (i.e 5% to 0.05)

   

NameAmount
A5%
B6.50%

Excel 2:

Here, the amount value showing as a number only. but still it is a percentage. (i.e 8 is a 8%)

   

NameAmount
C8
D6.7

Now I want to concatenate the above two table into one table and Amount Field should be one format . either with percent % symbol or value.

Result: (Concatenated Table)

 

NameAmount
A0.050
B0.065
C0.080
D0.067

How can I achieve this?

5 Replies
fvelascog72
Partner
Partner

Hi,

Try with:

Data:

Load

  Name,

  SubField(Amount, '%', 1)/100 as Amount

Inline [

Name, Amount

A, 5%

B, 6.50%

]

;

Concatenate

Load

  Name,

  SubField(Amount, '%', 1)/100 as Amount

Inline [

Name, Amount

C, 8

D, 6.7

]

;

Saludos

jeevays7
Partner
Partner
Author

Hi Federico,

Thanks for your reply.

Both files are reading from excel file. while reading values from excel file, i am not getting the % symbol.

I got only values. (ex:5% as 0.05 )

fvelascog72
Partner
Partner

Hi,

If you got numbers after load you can format the field where you want to use it.

Can you share a sample of your excel files?

Saludos.

jeevays7
Partner
Partner
Author

Hi Feder,

PFA.,

fvelascog72
Partner
Partner

Hi,

Sorry for the delay.

TRy with:

Data:

LOAD Name,

     Amount

FROM

(ooxml, embedded labels, table is Sheet1);

LOAD

  Name,

  Amount/100 as Amount

FROM

(ooxml, embedded labels, table is Sheet1);

Saludos