Format issue with percent symbol

[jeevays7]

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)

Name	Amount
A	5%
B	6.50%

Excel 2:

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

Name	Amount
C	8
D	6.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)

Name	Amount
A	0.050
B	0.065
C	0.080
D	0.067

How can I achieve this?

[fvelascog72]

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]

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]

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]

Hi Feder,

PFA.,

[fvelascog72]

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