topic Re: Format issue with percent symbol in QlikView

Format issue with percent symbol

jeevays7 — Tue, 10 Jul 2018 08:32:49 GMT

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?

Re: Format issue with percent symbol

fvelascog72 — Tue, 10 Jul 2018 08:45:17 GMT

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

Re: Format issue with percent symbol

jeevays7 — Tue, 10 Jul 2018 09:58:21 GMT

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 )

Re: Format issue with percent symbol

fvelascog72 — Tue, 10 Jul 2018 10:05:45 GMT

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.

Re: Format issue with percent symbol

jeevays7 — Tue, 10 Jul 2018 12:10:39 GMT

Hi Feder,

PFA.,

Re: Format issue with percent symbol

fvelascog72 — Wed, 11 Jul 2018 09:09:45 GMT

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